×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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:

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' ) 
This does not

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' ) 
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?

RE: GOING FROM DD/MM/YYYY TO MM/DD/YYYY

"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

RE: GOING FROM DD/MM/YYYY TO MM/DD/YYYY

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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

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.

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"

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close