Problem Note 68287: Filtering a date with the DATETIME format in a SAS® Studio query might return the message “ERROR: Invalid date/time/datetime constant”
 |  |  |  |
In SAS Studio, you might encounter the following error when filtering a date column in a query:
“ERROR: Invalid date/time/datetime constant”
This error mainly occurs when you are filtering a date column in which a DATETIME format is applied.
For example, you might encounter this issue if you complete steps similar to the following:
- Create a data set that contains a column with format DATETIME27.6.
- Create a new query and select the Date column on the Filters tab.
- Then, in the pop-up window, click the calendar icon and select a date (this example uses the 10th of August)
- Execute the query. It might fail because the code that is automatically generated by SAS Studio is incorrect, as shown below:
93 PROC SQL;
94 CREATE TABLE WORK.QUERY_FOR_TABLE AS
95 SELECT
96 t1.'DATE'n
97 FROM
98 A.TEST_SASSTUDIO t1
99 WHERE
100 t1.'DATE'n = 'Tuesday, August 10, 2021 12:00:00.000000 AM'dt
ERROR: Invalid date/time/datetime constant 'Tuesday, August 10, 2021 12:00:00.000000 AM'dt.
101 ;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
102 QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
94 CREATE TABLE WORK.QUERY_FOR_TABLE AS
95 SELECT
96 t1.'DATE'n
97 FROM
98 A.TEST_SASSTUDIO t1
99 WHERE
100 t1.'DATE'n = 'Tuesday, August 10, 2021 12:00:00.000000 AM'dt
ERROR: Invalid date/time/datetime constant 'Tuesday, August 10, 2021 12:00:00.000000 AM'dt.
101 ;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
102 QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
To work around this problem, you can edit the code, modify it, and re-execute the query. Alternately, you can replace the DATETIME format with DATE format.
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | SAS Studio for Viya | Linux for x64 | 5.2 | 2021.1.3 | Viya | Viya platform |
| Type: | Problem Note |
| Priority: | high |
| Date Modified: | 2023-02-02 14:38:16 |
| Date Created: | 2021-08-20 04:05:11 |