No Date Returned from Conversion

Question

There are values there for the 3 fields that make up the date here, but I get null back..so something is wrong with my syntax. I also want just the date (no time) `

   case when isdate(CAST(someTable.DATE_MM as varchar(8)) + CAST(someTable.DATE_DD as varchar(8)) + CAST (someTable.DATE_YY as varchar(8))) = 1
        then convert(date,CAST(someTable.DATE_MM as varchar(8)) + '/' + CAST(someTable.DATE_DD as varchar(8)) + '/' + CAST (someTable.DATE_YY as varchar(8))) else null end as BirthDate,

that's in my select statement and it's giving me NULL for the data. The values for those 3 fields are:

DATE_MM: 3
DATE_DD: 4
DATE_YY: 1959

the data type behind those 3 fields is int

You might want to have a look at http://stackoverflow.com/questions/266924/create-a-date-with-t-sql — Tejs, Nov 08 '12 at 16:30
@Tejs, that works but do you know how to get just the Date from the cast (strip out time)? — user1808194, Nov 08 '12 at 16:55
ok resolved I came up with doing a convert instead of cast via that example you sent from that other post. — user1808194, Nov 08 '12 at 16:58

score 0 · Answer 1 · answered Nov 08 '12 at 16:32

0

You merge 3 varchar values together, it will return a varchar, therefore your isdate will return false and your case will return the null value from the else clause.

answered Nov 08 '12 at 16:32

Hugo V

184
3

well then how do I combine them together. I don't want to do int + int+ int ...that's adding, not appending the parts together to make a date so that the convert will work. So I'm doing it via varchar so I can append those together... – user1808194 Nov 08 '12 at 16:42

score 0 · Answer 2 · answered Nov 08 '12 at 16:51

0

You could do it with dateadd instead.

select dateadd(day, DATE_DD - 1, 
               dateadd(month, DATE_MM - 1, 
                       dateadd(year,  DATE_YY - 1900, 0))) as BirthDate
from someTable

answered Nov 08 '12 at 16:51

Mikael Eriksson

136,425
22
210
281

No Date Returned from Conversion

2 Answers2