Use stored procedure to convert num/text to date? 1

[arniec]

I am running SQL Server 2000. I have a field which comes in as a string of numbers representing a date. For example:

122506

is christmas of this year.

70406

is the 4th of July.

I need to get this information stored in a DateTime field in the database. I was hoping to write a stored procedure on this but hours of attempts have ended in failure.

I have tried storing the number as both a bigint and a char with equal failure.

Can someone start me on the right path for this?

THANK YOU!

 

[gmmastros]

You better test this about 100 different ways. The incoming date is real ugly. No, it's actually VERY ugly. Because of this, there is a high likelyhood that you may get erroneous data. That being said, try this....

Declare @Temp VarChar(20)
Set @Temp = '70406'

select Convert(DateTime, right(@Temp, 2) + Left(Right('000000' + @Temp, 6), 4))

Let me break that down for you so that it makes sense, working from the inside out.

It appears as though any date prior to Oct 1, will be represented by a 7 character string (because the month number can be represented with a single digit). So we need to first format the number to a standard 6 digits.

Right('000000' + @Temp, 6) will add a leading 0 if need be. So 70406 will become 070406.

Taking the 2 right characters of this string will get the year. Taking the left 4 characters of the padded string will return the month and day (in that order).

So, just before converting to DateTime data type, 70406 becomes 060704. SQL Server's convert function will correctly interpret this as YYMMDD.

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[arniec]

Thank you thank you thank you!!!! That worked, and your explanation has allowed me to modify it to fit another scenario as well.

THANK YOU!

 

[gmmastros]

I'm glad it helped.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom