# Convert a date to yyyymmdd format

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
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

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

-
I used the Convert function for my default value when I use the Create Table sql statement, which I can see the 2 trailing spaces. –  Jack May 17 '13 at 6:20
What is the datatype and length of the column in the create table statement? Is it varchar(8)? varchar(50)? –  Raj May 17 '13 at 7:25
In your specific case, it really does not matter, but it is always a good practice to use varchar(n) Vs just varchar. Out of curiosity, why are you storing a date as varchar? Why not use the DATE datatype, which will make using date functions a lot easier? Not to mention that it will use only 3 bytes of space as compared to the 10 bytes a varchar(8) uses. –  Raj May 17 '13 at 9:51
SQL 2008 has new data types DATE and TIME. DATE stores only date and TIME stores only time :) –  Raj May 17 '13 at 10:13
@Jack stop worrying about format. SQL Server doesn't store a date/time as a string, nor should it. Worry about format when you are displaying the data on your web page or application - you should leave any string formatting to the last possible point. –  Aaron Bertrand May 20 '13 at 0:59