-1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
kaaazaaantip
  • 11
  • A `date` has no format, nor needs one. Which is why you are (correctly) using a `convert` to give it that format for display purposes. When I say correctly I mean that the use of `convert` per se is correct, even though you are not calling it correctly. The style 10 is `mm-dd-yy`, not `mm/dd/yyyy`. – GSerg Jul 01 '22 at 18:03
  • SQL Server Integration Services (SSIS) is made for such tasks. – Yitzhak Khabinsky Jul 01 '22 at 18:11
  • I am using this in azure and I need to basically get this date column in mm/dd/yyyy format in CSV. I cannot use SSIS in Azure data factory – kaaazaaantip Jul 01 '22 at 18:14
  • Once again - dates in a date (or similar) column have no defined format. They are stored as binary values that are unreadable. Whatever "format" you see in a CSV file is a product of the tool you are using to generate that file. If your goal is to force the use of a particular format in your CSV file, try searching the internet for suggestions about how to export dates in a particular format using ADF. But if the issue is about what you "see" when you query the database, the format is also a product of the tool you use for that. The column itself has **no format**. – SMor Jul 01 '22 at 19:19

2 Answers2

0

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.

zep426
  • 179
  • 9
  • 1
    Pretty sure OP wants 101 and not 10 – John Cappelletti Jul 01 '22 at 19:54
  • 1
    @JohnCappelletti I thought so too, and was going to put it in the answer....but I was a little confused as his posted SQL code had it with 10. But you are correct, if OP or anyone else wants a date in format `mm/dd/yyyy`, 101 would be the way to do it. – zep426 Jul 01 '22 at 20:01
0

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.

  1. Either impport the datetime column as string values, and then add a new column with datetime datatype and convert the values using string manipulation.

  2. 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

BdR
  • 2,770
  • 2
  • 17
  • 36