Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Which sql command is recommended to convert a date to yyyymmdd?

1) convert(varchar(8), getdate(), 112) 

or

2) convert(varchar, getdate(), 112)

I notice that if I used I use the 2nd one, it seems that Microsoft will append two space () after the date. (e.g. [20130705] - notice the two space after the value 20130705)

So, is it recommended to use the 1st sql statement?

share|improve this question
    
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
add comment

1 Answer 1

up vote 2 down vote accepted

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

share|improve this answer
    
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
3  
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
1  
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
1  
@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
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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