Problem using MONTH() function

[PabloBrenner]

Guys,

I'm having a hard time trying to figure this out:

I have a column with date format, but when trying to user MONTH() oir YEAR() to extract information from it, the return is a blank cell.

Attached a pic of the query result.

Thanks a lot!

Pablo

[Reeza · Accepted solution]

That's a DATETIME field, not a DATE field. 

Use datepart() to convert to a date and then extract the month/year as desired.

month(datepart(date_var))

[PabloBrenner]

Worked fine! Thanks a lot for the help and for explaining about the date format!

[ballardw]

Please slap any of your developers or data designers for calling that a date if that is why you thought it was a date value. Slap them twice if they are forcing continuing the time value parts around if they are ALWAYS 00:00:00. There seems to be some sloppy stuff regarding dates and possibly Microsoft defaults that are becoming very common place.

[PabloBrenner]

I'm reading a table from an Oracle DB, at least when I read it in Oracle SQL Developer it is showing as a date field, but I will take some time to look into it, since we are thinking about automating some processes in SAS, this might turn out to be an issue upfront.

Thanks for the comment!

[Sven111]

If your reading from an Oracle DB it may not be the designers fault.  Oracle doesn't have a standard date only type.  Even the type called DATE has hours, minutes and seconds included whether you want them or not.  As a result when SAS pulls a DATE column from Oracle it treats it as a DATETIME, since that's really what it is.