GOING FROM DD/MM/YYYY TO MM/DD/YYYY
GOING FROM DD/MM/YYYY TO MM/DD/YYYY
(OP)
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:
This does not
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?
When I then try to get them back into the mm/dd/yyyy format, FORMAT chokes.
This works:
CODE --> t-sql
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' )
CODE --> t-sql
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' )
How can I get these dates into the mm/dd/yyy format?
Talk To Other Members
RE: GOING FROM DD/MM/YYYY TO MM/DD/YYYY
Chriss
RE: GOING FROM DD/MM/YYYY TO MM/DD/YYYY
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
RE: GOING FROM DD/MM/YYYY TO MM/DD/YYYY
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!
RE: GOING FROM DD/MM/YYYY TO MM/DD/YYYY
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.
https://www.sqlservercentral.com/articles/how-to-format-dates-in-sql-server-hint-dont-use-format
--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row"