7

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

  1. convert(varchar(8), getdate(), 112); or
  2. 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?

  • no direct reference to your question msdn.microsoft.com/en-us/library/ms176089.aspx – bummi May 17 '13 at 6:05
  • 2
    I would recommend to always explicitly specify a length for a varchar. In different places, you get different default lengths; as a parameter to a procedure, you get a default length of 1 character - typically not what you expect. – marc_s May 17 '13 at 11:03
16

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.