Convert to SQL Date from decimal DDMMYY

As it says – I’ve come across this before, so now I’ll dump my memory here having reinvented this old wheel.

The basic steps look like this, using April 1st, 2011 as a sample:

declare @orgvalue decimal(8,0)
set @orgvalue = 10411

select @orgvalue % 100 * 10000 + FLOOR(@orgvalue / 100) % 100 * 100 + FLOOR(@orgvalue / 10000)

 

First the year is isolated, then the month and then the date, and the result is obtained adding 110000 + 400 + 1 = 110401.

There is absolutely no error-handling here, so suppose you want to be absolutely sure that the result is actually a date, the easiest way is to actually cast it to a Date – but then you hav to cast via a char value to get it right.

select Convert(Date, 
        Convert(char(6), @orgvalue % 100 * 10000 + FLOOR(@orgvalue / 100) % 100 * 100 + FLOOR(@orgvalue / 10000),112))

 

This will not handle single digit year numbers, however, so in case you need that – and you’re sure about the century – you can do it like this:

select Convert(Date, 
        Convert(char(8), 20000000 + @orgvalue % 100 * 10000 + FLOOR(@orgvalue / 100) % 100 * 100 + FLOOR(@orgvalue / 10000),102))

 

Hope I won’t have to figure this out all over again next ime I need it…

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s