How to get DATE column in SQL Server in mm/dd/yyyy format and export it to CSV-CodePudding

I have a requirement to store data in a column in SQL Server with mm/dd/yyyy format. Which in my mind I would create this column with DATE datatype and use CONVERT() to convert it in mm/dd/yyyy format

CREATE TABLE [dbo].TempDate 
(
    CompanyID nvarchar (512) NULL,
    VacDate date NULL,
    OffDate date NULL,
    Duration time NULL
)

SELECT CONVERT(varchar(10), VacDate, 10) AS "StartDate"
FROM [dbo].TempDate;

No issues, I get the date in mm/dd/yyyy format. The problem is that I need to then import this table to CSV format and the date in the table is in its standard yyyy-mm-dd and so that's what gets imported in CSV. I need the date in CSV to be in mm/dd/yyyy format. I tried to INSERT into this table with CONVERT, but the date doesn't convert. Is there a way to STORE DATE in mm/dd/yyyy column right out of the gate? Like in Oracle its possible to define the column in a certain format.

Thank you for any thoughts on this.

CodePudding user response：

While I believe your focus should be on the query that is getting the data for the export, if you really need it in the database you could add a computed column that displays a string representation of the data, and use that column for the export.

Alter table TempDate
Add VacDataComputed As CONVERT(varchar(10), VacDate, 10)

Edit: As mentioned, you are using 10 for the style parameter, which will render mm-dd-yyyy. If you want it in mm/dd/yyyy format, use 101. You can see more style formats here.

CodePudding user response：

Your question is a bit unclear. Do you mean the CSV file contains MM/dd/yyyy format, and you want to import that into the SQL table as datetime values? So values like 12/31/2015, 07/15/2014 etc. are in the csv data file?

If that is the case I think you have two options.

Either impport the datetime column as string values, and then add a new column with datetime datatype and convert the values using string manipulation.
or, just change the CSV input file and reformat the datetime column before importing it, using Notepad CSV Lint plug-in or Python or something