SQL - Change decimals to date

eddie736
eddie736 used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
select dateadd(month, month_col - 1, dateadd(day, day_col - 1, convert(datetime, cast(year_col as varchar(4)) + '01-01', 120)))
  from yourtable
output

#dateformat (varaible,  'yyyy-mm-dd')#
<--- Bows to Angell III the god of EE :)

CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Author

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.
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
then you have year_col value < 1900, or null ...

Author

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?
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
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

Author

Commented:
Perfect!! Thanks!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial