SQL Convert yyyymmdd string to mm/dd/yyyy

Question

Question

Paul Riker · Nov 16, 2017

#SQL #Caché

Any suggestions on the most efficient way to convert yyyymmdd string to mm/dd/yyyy?

Discussion (7)0

Log in or sign up to continue

Kyle Baxter · Nov 16, 2017

The correct way to do this is with TO_DATE:

select TO_DATE('19850720','YYYYMMDD')

That will convert the text in the first argument (a string) using the format in the second parameter into a date, which will be displayed using the current selectmode. Try this in the Management Portal.

2 0

score 0 · Answer 1 · 2017-11-16T11:44:24-05:00

Paul Riker · Nov 16, 2017

Is there a better approach than this?

select convert(varchar(20),cast ('19770110' as datetime),101)

0 0

score 1 · Answer 2 · 2017-11-16T11:58:56-05:00

Please try this Cache Object script command.

w $ZD($ZDATEH("YYYYMMDD",5))

Example:

w $ZD($ZDATEH("20170925",5)).

Output: 09/25/2017

score 2 · Answer 3 · 2017-11-16T14:16:28-05:00

Evgeny Shvarov · Nov 16, 2017

This is beautiful!

2 0

score 1 · Answer 4 · 2017-11-17T03:29:17-05:00

Funny, I learnt a similar approach by the numbers many moons ago.

USER>w $tr("12/34/5678",56781234,20171116)

11/16/2017

score 0 · Answer 5 · 2017-11-17T09:44:12-05:00

Robert Cemper · Nov 17, 2017

much faster and straight forward string conversion then $ZD* stuff doing unnecessary conversions .

0 0

score 6 · Answer 6 · 2017-11-16T12:58:32-05:00

Alexander Koblov · Nov 16, 2017

 select $Translate('Mm/Dd/YEAR','YEARMmDd','20171116')

6 0