Need help converting chars to a date result

Question

Post reply

Need help converting chars to a date result

RoadRunner

SSC-Addicted

Points: 409
More actions
January 12, 2004 at 2:08 pm

#108648

I have 2 columns in a SQL2000 db; one is one char and contains 3 or 4 – this is the year, 2003 or 2004 respectfully. The second column has 3 chars like 012 or 029 or 057 etc. – this represents the day of year, 012 is January 12, 029 is January 29, 057 is February 26 etc. How do I combine these two fields to get a date output in the result set? Ex. I need 4 & 029 to return 1/29/2004

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Not sure if this will really help, but what about

declare @myyear int

declare @mydays int

declare @startofyear datetime

set @myyear = 3

set @mydays = 57

set @startofyear =dateadd(d,@mydays-1,dateadd(yy,@myyear,cast('01.01.2000' as datetime)))

select @startofyear

------------------------------------------------------

2003-02-26 00:00:00.000

(1 row(s) affected)

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

gljjr SSChampion Points: 10080 More actions · Answer 2

Try this...

SELECT YourMonth = datepart(mm,CONVERT(datetime,convert(int,'057')))

, YourDay = datepart(dd,CONVERT(datetime,convert(int,'057')))

, YourDate = convert(varchar, convert(varchar, datepart(mm,CONVERT(datetime,convert(int,'057')) ) ) + '/'

+ convert(varchar, datepart(dd,CONVERT(datetime,convert(int,'057')) )) + '/'

+ '20' + '04')

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

Revising Frank's solution for the OP's data types:

declare @myyear char, @mydays char(3)

set @myyear = '3'

set @mydays = '057'

select dateadd(d,@mydays-1,'200' + @myyear)

--Jonathan

edwin_fredrick Old Hand Points: 364 More actions · Answer 4

edwin_fredrick

Old Hand

Points: 364

January 12, 2004 at 11:22 pm

#489674

@mydays-1 works though @mydays is char type.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Hm, obviously I missed the char description. Gladly char with leading 0s will turn without problems into int.

Jonathan, nice to see you started correcting wrong answers again!!!

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

RoadRunner SSC-Addicted Points: 409 More actions · Answer 6

RoadRunner

SSC-Addicted

Points: 409

January 13, 2004 at 6:13 am

#489714

Thank you all!!!