GOING FROM DD/MM/YYYY TO MM/DD/YYYY 1

[grnzbra]

I have an Excel spreadsheet with dates formatted dd/mm/yyyy. No matter how I tell Excel to format these columns, SQL always imports them as VARCHAR(255)
When I then try to get them back into the mm/dd/yyyy format, FORMAT chokes.
This works:

DECLARE @D		VARCHAR(10)
DECLARE @DDt		DATE
SET @D = '3/25/2022'
SET @DDt = CAST(@D AS DATE)

SELECT FORMAT(@DDt,'dd/MM/yyyy', 'en-US' )

This does not

DECLARE @D				VARCHAR(10)
DECLARE @DDt			DATE
SET @D = '25/3/2022'
SET @DDt = CAST(@D AS DATE)

SELECT FORMAT(@DDt,'MM/dd/yyyy', 'en-US' )

Using CONVERT instead of CAST also does not work in the second case.
How can I get these dates into the mm/dd/yyy format?

 

[Chris Miller]

Take a look into the books online:

https://docs.microsoft.com/de-de/sq...dateformat-transact-sql?view=sql-server-ver16

Chriss

 

[Andrzejek]

Excel spreadsheet with dates formatted dd/mm/yyyy" - that's just the format you want to see. Excel actually keeps dates as numbers (so is your data base), and formats / displays them (for your convenience) as dates you can recognize.

Here: A1 B1 and C1 have the same value (1), column A is formatted as date, so is column B (different Date format), column C is formatted as Number
The same goes for row 2 and row 3

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

Are you IMPORTING an Excel file?

I'd
1) SaveAs the sheet as a .csv then
2) IMPORT the .csv TEXT file.

As Andy stated, the Excel Date Values are numbers as in his column C.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[JeffModen]

Just import them (the character version) into a DATE datatype column and then you can do what you want from there (hint: Using CONVERT... not FORMAT).

And don't use the "underlying numbers" (Date Serial Numbers) from Excel because Excel still thinks that 1900 was a leap year and it is not. They tried to make up for it by making the Date Serial Number for 01/01/1900 = 1 instead of ZERO like it is in SQL Server.

I also strongly recommend that you stop using FORMAT. It's usually at least 27 times slower than any other conversion you might dream up even with multiple CONVERTs. See the following article for that bit of fun.

[URL unfurl="true"]https://www.sqlservercentral.com/articles/how-to-format-dates-in-sql-server-hint-dont-use-format[/url]

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row