SQL - Change decimals to date

For some reason, instead of having a date field in my data, I have three separate fields:

Year
Month
Day

All are decimals. Year is 4 digits, month and day are 2 digits each. How do I convert this into a date and/or filter all the data on a range of dates?
eddie736Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, actually my fault. I omitted a "-"
select dateadd(month, month_col - 1, dateadd(day, day_col - 1, convert(datetime, cast(year_col as varchar(4)) + '-01-01', 120)))
  from yourtable

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
select dateadd(month, month_col - 1, dateadd(day, day_col - 1, convert(datetime, cast(year_col as varchar(4)) + '01-01', 120)))
  from yourtable
0
 
plusone3055Commented:
output

#dateformat (varaible,  'yyyy-mm-dd')#
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
plusone3055Commented:
<--- Bows to Angell III the god of EE :)

0
 
eddie736Author Commented:
Hi angell:

I received this error when I used your soloution:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
then you have year_col value < 1900, or null ...
0
 
eddie736Author Commented:
The years don't appear to have nulls or <1900s, but the days and months have one digit where <10. Would that be the problem?
0
 
eddie736Author Commented:
Perfect!! Thanks!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.