Paul Riker · Nov 16, 2017

SQL Convert yyyymmdd string to mm/dd/yyyy

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

0 12,093
Discussion (7)1
Log in or sign up to continue

Is there a better approach than this?

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

Please try this Cache Object script command.



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

Output: 09/25/2017

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

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


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

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.

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