topic Re: Problem using MONTH() function in SAS Enterprise Guide

Problem using MONTH() function

PabloBrenner — Thu, 09 Mar 2017 21:22:26 GMT

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

Re: Problem using MONTH() function

Reeza — Thu, 09 Mar 2017 21:24:46 GMT

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))

Re: Problem using MONTH() function

PabloBrenner — Thu, 09 Mar 2017 21:27:46 GMT

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

Re: Problem using MONTH() function

ballardw — Thu, 09 Mar 2017 23:00:40 GMT

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.

Re: Problem using MONTH() function

PabloBrenner — Fri, 10 Mar 2017 12:00:52 GMT

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!

Re: Problem using MONTH() function

Sven111 — Fri, 10 Mar 2017 16:30:53 GMT

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.