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

grnzbra · Sep 7, 2022

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:

Code:

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

Code:

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 · Sep 7, 2022

Take a look into the books online:

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

Chriss

Andrzejek · Sep 7, 2022

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 · Sep 7, 2022

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,
_{Just traded in my OLD subtlety...

for a NUance!}
"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 · Dec 18, 2022

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

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

grnzbra

Programmer

Chris Miller

Programmer

Andrzejek

Programmer

SkipVought

Programmer

JeffModen

Programmer

Similar threads

Part and Inventory Search

Sponsor