Sql-server – Convert a date to yyyymmdd format

date formatsql serversql-server-2008type conversion

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?

Best Answer

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

Related Solutions

Sql-server – SQL Globalization – DateTimeOffset

Use the DateTimeOffset datatype. Each server would store the data locally in its own timezone, storing the offset from UTC as well. When data is gathered to a central area, you can easily convert to UTC for comparison purposes.

SQL Server – Converting varchar Date to Date Format (dd/mm/yy)

I understand your problem to be how to successfully convert the string into a DATE value. An undelimited string of integers is assumed to be Year-Month-Day order. (And, of course, I agree with the comments that dates should be stored in DATE data types in the database.)

Reviewing the MSDN CONVERT documentation does not show a built-in conversion for your string, but it is easy to work around.

http://msdn.microsoft.com/en-us/library/ms187928.aspx -- CAST and CONVERT

I changed the month to 8 to make it easier to double check. Using the CONVERT style option 3, you can do the following:

DECLARE @String VARCHAR(10);
DECLARE @DateValue DATE;
SET @String = '250809'; 

-- Convert your undelimited string DDMMYY into a DATE
-- First: Add / between the string parts.
SET @STRING = SUBSTRING(@String,1,2)+'/'+
     SUBSTRING(@String,3,2)+'/'+SUBSTRING(@String,5,2);
-- Second: Convert using STYLE 3 to get DD/MM/YY interpretation
SELECT @DateValue = CONVERT(Date, @String, 3);

-- Using the DATE 
-- Select the value in default Year-Month-Day
SELECT @DateValue AS DefaultFormat;    
-- Select the value formatted as dd/mm/yy
SELECT CONVERT(VARCHAR(20),@DateValue,3) AS [DD/MM/YY];

The results of the last two selects are:

DefaultFormat
-------------
2009-08-25

DD/MM/YY
--------
25/08/09

To get your DATE formatted in the way you want it, you have to insert the '/' delimiters then use the STYLE 3 in converting from the string to the DATE. (I am sure that there are other workarounds and conversion styles that would work as well.)

Likewise when displaying the DATE as you desire, you need to use STYLE 3

Best Answer

Related Solutions

Sql-server – SQL Globalization – DateTimeOffset

SQL Server – Converting varchar Date to Date Format (dd/mm/yy)

Related Question