Convert a 2 digit year to 4 digits?

[Glowworm27]

Hello all,

I have a sql query to return vehicle information, that I need to feed to another vendor.

problem is some of the data has 2 digit year, and some has 4 digit year, the vendor requested that I ALWAYS send 4 digit year info.

I was hoping that someone has a solution that would fit my situation.

Thanks a Bunch!


George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[Glowworm27]

I figured it out, it was actualy quite simple once I set my mind to it.

here is the problem, some of the model year data is 2 digits some of it is 4 digits like this

vehicle modelyear
03
2001
1996
98
85

but I need to always send a 4 digit year to the vendor like this

vehicle modelyear
2003
2001
1996
1998
1985

how I did this was to concantanate the text '01/01/' to the front of the year, then take the datepart( yyyy, date) which will return the year in a 4 digit format like this

DATEPART ( yyyy ,('01/01/'+ Vehicles.ModelYear)) as 4DigitYear

thanks for your help in the past and I hope this will help someone in the future

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[donutman]

Are you saying the date data is stored in a varchar field?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Glowworm27]

Yes we were only keeping the vehicle model year, and it has to be varchar so that if a 2 digit number like 03 was entered it would stay 03, and not become a 3.

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips