Desktop productivity for business analysts and programmers

Query filter: WEEK() and similar date/time functions do not work

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Query filter: WEEK() and similar date/time functions do not work

[ Edited ]

Hi.

I'm writing a very simple query from a table that contains a column (CREATE_DT) which, elsewhere SAS recognizes** as a valid date field without any complaints.  Well, when I add a computed column to select, as an experiment, just to show me the week number of a returned CREATE_DT, it just won't work.  I have a very simple computed column with the following statement:

WEEK(CREATE_DT) AS WeekNum


WeekNum above returns only dashes for all records.  Even MONTH(), which works in WHERE clauses, also returns only dashes.  I tried out several functions in several columns with CREATE_DT as arguments, including DATE, TIME, HOUR, QUARTER, JULIANDATE, WEEKDAY, etc.  Only the functions DATE, HOUR, and SECOND returned values (and the values are correct).  All the rest returned dashes.

 

 

Are these functions broken when used in computed columns...?  How do I get WEEK() and others to work as expected???


**When I say recognizes elsewhere, I mean that (e.g.)

MONTH (t1.CREATE_DT)

returns only records with CREATE_DT in a month of November, as expected.

 


Accepted Solutions
Solution
‎11-15-2017 04:14 PM
Super User
Posts: 22,874

Re: Query filter: WEEK() and similar date/time functions do not work

Posted in reply to complimentarian

complimentarian wrote:

Hi.

I'm writing a very simple query from a table that contains a column (CREATE_DT) which, elsewhere SAS recognizes** as a valid date field without any complaints.  Well, when I add a computed column to select, as an experiment, just to show me the week number of a returned CREATE_DT, it just won't work.  I have a very simple computed column with the following statement:

WEEK(CREATE_DT) AS WeekNum


WeekNum above returns only dashes for all records.  Even MONTH(), which works in WHERE clauses, also returns only dashes.  I tried out several functions in several columns with CREATE_DT as arguments, including DATE, TIME, HOUR, QUARTER, JULIANDATE, WEEKDAY, etc.  Only the functions DATE, HOUR, and SECOND returned values (and the values are correct).  All the rest returned dashes.

 

 

Are these functions broken when used in computed columns...?  How do I get WEEK() and others to work as expected???


**When I say recognizes elsewhere, I mean that (e.g.)

MONTH (t1.CREATE_DT)

returns only records with CREATE_DT in a month of November, as expected.

 


What's the FORMAT and TYPE on the column?

 

If it's a DATETIME field, you need to use DATEPART() first to get the date component. If it's a character field you need to convert it to a numeric field with the appropriate format.

View solution in original post


All Replies
Solution
‎11-15-2017 04:14 PM
Super User
Posts: 22,874

Re: Query filter: WEEK() and similar date/time functions do not work

Posted in reply to complimentarian

complimentarian wrote:

Hi.

I'm writing a very simple query from a table that contains a column (CREATE_DT) which, elsewhere SAS recognizes** as a valid date field without any complaints.  Well, when I add a computed column to select, as an experiment, just to show me the week number of a returned CREATE_DT, it just won't work.  I have a very simple computed column with the following statement:

WEEK(CREATE_DT) AS WeekNum


WeekNum above returns only dashes for all records.  Even MONTH(), which works in WHERE clauses, also returns only dashes.  I tried out several functions in several columns with CREATE_DT as arguments, including DATE, TIME, HOUR, QUARTER, JULIANDATE, WEEKDAY, etc.  Only the functions DATE, HOUR, and SECOND returned values (and the values are correct).  All the rest returned dashes.

 

 

Are these functions broken when used in computed columns...?  How do I get WEEK() and others to work as expected???


**When I say recognizes elsewhere, I mean that (e.g.)

MONTH (t1.CREATE_DT)

returns only records with CREATE_DT in a month of November, as expected.

 


What's the FORMAT and TYPE on the column?

 

If it's a DATETIME field, you need to use DATEPART() first to get the date component. If it's a character field you need to convert it to a numeric field with the appropriate format.

New Contributor
Posts: 3

Re: Query filter: WEEK() and similar date/time functions do not work

Taking your advice,

WEEK(DATEPART(CREATE_DT))

Works as expected.  Thank you!

 

 

But this is still very confusing to me.  Why would WHERE MONTH(CREATE_DT)=11 filter as expected, but a computed column MONTH(CREATE_DT) needs DATEPART() to make it understand the date?

Super User
Posts: 22,874

Re: Query filter: WEEK() and similar date/time functions do not work

Posted in reply to complimentarian

I wouldn't expect them to behave the same and would double check my code and results.

 


complimentarian wrote:

Taking your advice,

WEEK(DATEPART(CREATE_DT))

Works as expected.  Thank you!

 

 

But this is still very confusing to me.  Why would WHERE MONTH(CREATE_DT)=11 filter as expected, but a computed column MONTH(CREATE_DT) needs DATEPART() to make it understand the date?


 

Super User
Posts: 13,084

Re: Query filter: WEEK() and similar date/time functions do not work

Posted in reply to complimentarian

Date values are number of days from 01Jan1960.

Datetime values are number of seconds from 01Jan1960:00:00:00.

 

So except when your datetime values are kind of close to 1960 many of the date functions will not return any value. The date functions basically do not work for any date after 31 Dec 20000 (yes year 20,000).

Please see:

data junk;
   x=6589335;
   y=year(x);
   put "X as datetime: " x= datetime20. +1 "X as date: " x= mmddyy10. 'year: ' y=;
   x2 = x+100;
   y = year(x2);
   put "X2 as datetime: " x2= datetime20. +1  'year for X2: ' y=;
run;

 

Notice that the date displayed using the mmddyy10 format doesn't display because 5 digits don't fit into the 4 allowed by the format. None of the SAS supplied formats can display a year past 9999 correctly.

After we add 100 days to the date we get an error for the Year() function because the date is out of range.

So generally the date functions will not return anything useful when applied to a DATETIME value.

 

I would examine my data very carefully to see if that "filter" actually worked. Post example data in the form of data step to see others replicate your results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 260 views
  • 0 likes
  • 3 in conversation