Desktop productivity for business analysts and programmers

Problem using MONTH() function

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Problem using MONTH() function

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


SAS.JPG

Accepted Solutions
Solution
‎03-09-2017 04:27 PM
Super User
Posts: 19,039

Re: Problem using MONTH() function

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

View solution in original post


All Replies
Solution
‎03-09-2017 04:27 PM
Super User
Posts: 19,039

Re: Problem using MONTH() function

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

Occasional Contributor
Posts: 5

Re: Problem using MONTH() function

Worked fine! Thanks a lot for the help and for explaining about the date format!
Super User
Posts: 11,107

Re: Problem using MONTH() function

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.

Occasional Contributor
Posts: 5

Re: Problem using MONTH() function

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!

Frequent Contributor
Posts: 82

Re: Problem using MONTH() function

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 242 views
  • 1 like
  • 4 in conversation