Convert a date to yyyymmdd format

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?

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.

Raj

StackExchange Database Administrators Q#42395, answer score: 16