converting date field format

I have a field with birthday as either '7/08/1986' or '07/08/1986'.

I want to convert the value to like this '1986/08/07'. Simply converting from DD/MM/YYYY to YYYY/MM/DD. Kindly note down this is all in SQL Server 2005.

Additionally, if the date is in single digit means still the output shd be shown in double digit like '07' not '7'.

How do I do this conversion?

Your kind replies开发者_JAVA百科 will help me out. My thanks in advance.

first don't store these dates as strings, use the datetime (Transact-SQL) or smalldatetime (Transact-SQL) data type (sql server 2008 has a date (Transact-SQL) for dates only), then you can easily format it using the built in tool CONVERT (Transact-SQL).

By storing dates as strings, you can easily get invalid dates like '2/29/2010' or '13/13/2010'. Also it takes 10 bytes to store 'mm/dd/yyyy' in a string, while it takes 8 bytes to store a datetime, and only 4 bytes to store a smalldatetime value. When you store string dates, you end up having difficult times manipulating the values (like the problem in this question). if you use datetime or smalldatetime your code would be like this:

DECLARE @YourTable table (YourDate datetime)
INSERT @YourTable VALUES ('12/1/2010 13:45')
INSERT @YourTable VALUES ('20101201 8:00am')
INSERT @YourTable VALUES ('2010-12-01')

SELECT
    CONVERT(char(10),YourDate,111)      AS DateOnly
        ,CONVERT(char(23),YourDate,121) AS FullDateTime
        ,CONVERT(char(12),YourDate,107) AS MonthNameOf
        ,CONVERT(char(12),YourDate,114) AS TimeOnly
        --there are many more output display formats
    FROM @YourTable

OUTPUT:

DateOnly   FullDateTime            MonthNameOf  TimeOnly
---------- ----------------------- ------------ ------------
2010/12/01 2010-12-01 13:45:00.000 Dec 01, 2010 13:45:00:000
2010/12/01 2010-12-01 08:00:00.000 Dec 01, 2010 08:00:00:000
2010/12/01 2010-12-01 00:00:00.000 Dec 01, 2010 00:00:00:000

(3 row(s) affected)

To fix your existing string columns follow this example:

set up your existing table (OP doesn't need to do this, as this table already exists for them):

CREATE TABLE HasBadStringDates (DateOf varchar(10) null)
INSERT HasBadStringDates VALUES ('1986/08/07')
INSERT HasBadStringDates VALUES ('7/08/1986')
INSERT HasBadStringDates VALUES ('07/08/1986')

select * from HasBadStringDates

OUTPUT:

DateOf
----------
1986/08/07
7/08/1986
07/08/1986

(3 row(s) affected)

fix your existing table:

ALTER TABLE HasBadStringDates
     ALTER COLUMN DateOf datetime NULL
GO

select CONVERT(char(10),DateOf,111) AS DateOnly from HasBadStringDates

OUTPUT:

DateOnly
----------
1986/08/07
1986/07/08
1986/07/08

(3 row(s) affected)

You can convert the input birthday to a datetime field, then immediately convert it to varchar again. Try this:

select convert(varchar(10), convert(datetime, '7/8/1986'), 111)

The output should be '1986/07/08', a varchar.

CONVERT(nvarchar(30), GETDATE(), 111)

Will get you to YYYY/MM/DD