question

michelle avatar image
michelle asked

How to convert DD/MM/YYYY to YYYYMMDD ?

I have an answer set with date format DD/MM/YYYY. Anyone knows how to convert to YYYYMMDD ?

Thanks in advance. Michelle

date-functions
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
is the date coming back as a field of type date, or as a string of some sort (char / varchar)?
0 Likes 0 ·
Scot Hauder avatar image
Scot Hauder answered
SELECT CONVERT(varchar(8),snap_d,112)

or if you are working with strings

SELECT CONVERT(varchar(8),CAST(snap_d AS DATE),112)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

michelle avatar image michelle commented ·
Hi Scot, I don't get it. The name of the column is snap_d, where do I place snap_d in the sql to get YYYYMMDD?
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered

If your "snap_d" column is coming in as a date field, then the

select convert(varchar(8), snap_d, 112)

proposed by Scot should be fine.

If, on the other hand, it's coming in as a string (char / varchar) field, then you'll need to do a bit of string slicing and dicing:

select RIGHT(snap_d,4) + '' + SUBSTRING(snap_d,4,2) + '' + LEFT(snap_s,2)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

rosycanfield avatar image rosycanfield commented ·
Thanks it help me to resolve my issue.
0 Likes 0 ·
dziekan_tek avatar image
dziekan_tek answered
It's easier way... Data --> Text as a column(s) --> divide each data (yyyy, then mm, then dd) and next... lets assume that column A (A2 first cell) is our "wrong" date, so... concut in cell E2 will looks like: =B2&"-"&C2&"-"&D2 You need only replace 1-digit number like 1, 2, 3... to 01, 02, 03. And that's all :-) I hope that I help you in this easiest way. You can of course write macro - it's easy. Regards, dziekan_tek@o2.pl
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Patrick avatar image
Patrick answered
SELECT CONVERT(VARCHAR(8), GETDATE(), 112) sysd; SELECT convert(varchar(100),getdate(),24) syst;
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dpk.bhanushali avatar image
dpk.bhanushali answered
SELECT TO_NUMBER(TO_CHAR(COLUMN_NAME, 'YYYYMMDD'))AS ALIAS
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.