How to convert string Data into SQL 2005 Datetime Data... 1

[patiya]

Hellow there,

I have a SQL 2005 Table with 2 Columns:

Column Name: Data Type:
PERIOD_END_DATE Nvarchar(50)
Date_Period_End Datetime
___________________________________________________________

Currently Data in Column's:
PERIOD_END_DATE Date_Period_End
010909 Null
123108 Null
012209 Null

My Question is how do I update Column Date_Period_End (mm/dd/yyyy format)from Column PERIOD_END_DATE?

My desired result after T-SQL:

PERIOD_END_DATE Date_Period_End
010909 01/09/2009
123108 12/31/2008
012209 01/22/2009

Basically I would like to update Datetime Type column Date_Period_End from Nvarchar Type column PERIOD_END_DATE in the same table. Any help will be sincerely appreciated..

Thank You

 

[gmmastros]

The problem is... you could have some bad data and not know it. So... these types of conversion are always a pain. There are also international issues to worry about. Based on your sample data, it appears as though your original data is MMDDYY. If your dates were in YYMMDD format, this would be a lot simpler.

If you have no invalid dates....

Update YourTableName
Set    Date_Period_End = Convert(DateTime, Right(PERIOD_END_DATE, 2) + SubString(PERIOD_END_DATE, 3, 2) + Left(PERIOD_END_DATE, 2))

If all your dates are valid, the code I show should run just fine. If you get an error, it's probably because you have an invalid value somewhere.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[patiya]

Thanks George