CODEWITHSUNDEEP
sqlsql-server-2008date-format
dtc
dtc

Reputation: 10296

Convert CMMDDYY into date in SQL Server

If I have a date string in the format of CMMDDYY

C = 0, 1 or 2 where 0 represents 19, 1 represents 20 and 2 represents 21

Example:

1022511 would be 02/25/2011 in mm/dd/yyyy format.

Is there a way to convert this into mm/dd/yyyy format in sql server?

Is CMMDDYY even a valid date format? I haven't found much information on it.

Upvotes: 0

Views: 642

Answers (2)

Preet Sangha
Preet Sangha

Reputation: 65496

The Year algorithm is simply: (19 + int(C))*100 + int(yy)

Try this:

declare @st nvarchar(7)
set @st = '1030609'

select 
    cast(
        substring(@st,2,2) + '/' 
        + substring(@st,4,2) + '/' 
        + cast((19 + cast(substring(@st,1,1) as int) )* 100 
                       + cast(substring(@st,6,2) as int) as nvarchar)
    as datetime)

This outputs:

enter image description here

Upvotes: 1

Arj
Arj

Reputation: 2046

It doesn't sound like a built in date format but you should be able to find it on msdn if it is. If not you could write a function in SQL server that parses a string in that format to a DateTime object

Upvotes: 1

Related Questions