How does sql convert date to yyyymmdd?

353 Asked by dipesh_9001 in SQL Server , Asked on Mar 13, 2023

Which SQL command is recommended to convert a date to yyyymmdd format?

convert(varchar(8), getdate(), 112); or
convert(varchar, getdate(), 112)
I notice that if I use the second one, it will append two spaces after the date. (e.g. [20130705] - notice the two space after the value 20130705)

Is it recommended to use the first SQL statement?

Answered by Diya tomar

sql convert date to yyyymmdd by default, as documented in MSDN, if no length is specified for varchar it will default to 30 when using CAST or CONVERT and will default to 1 when declared as a variable.

To demonstrate, try this :

DECLARE @WithLength varchar(3),@WithoutLength varchar;

SET @WithLength = '123';

SET @WithoutLength = '123';

SELECT @WithLength,@WithoutLength

This is very dangerous, as SQL Server quietly truncates the value, without even a warning and can lead to unexpected bugs. However, coming to the topic in question, in the given scenario, with or without length does not make any difference between the two statements and I am unable to see the 2 trailing spaces that you are talking about. Try this:

SELECT CONVERT(varchar(8), GETDATE(), 112) 

UNION ALL 

SELECT DATALENGTH(CONVERT(varchar(8), GETDATE(), 112)) 

UNION ALL 

SELECT CONVERT(varchar, GETDATE(), 112)

UNION ALL

SELECT DATALENGTH(CONVERT(varchar, GETDATE(), 112))

You will notice that the DATALENGTH() function returns 8 in both cases.

How does sql convert date to yyyymmdd?

Your Answer