🔒 This topic is solved and locked.
Need further help from the community? Please
sign in and ask a new question.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 09-09-2015 03:44 PM
(1334 views)
How do I create a date field with a value using a CASE WHEN statement? Here is my statement:
CASE WHEN t1.year = 24 then '01MAY2015:00:00:00'
END
1 ACCEPTED SOLUTION
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Problem - the date returned is today's date. Statement:
CASE WHEN t1.SCHOOL_YEAR_ID = 18 THEN DATETIME('01MAY2015:00:00:00'DT)
END
CASE WHEN t1.SCHOOL_YEAR_ID = 18 THEN DATETIME('01MAY2015:00:00:00'DT)
END
4 REPLIES 4
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
You probably want to use a date (or as in your example a datetime) literal. If you want a DATE then use '01MAY2015'D if you want a DATETIME value then use '01MAY2015:00:00:00'DT. You could also use a function call such as MDY(5,1,2015). You should also attach a format to the variable so the value will be displayed properly.
PROC SQL;
CREATE TABLE WANT AS
SELECT *
, CASE WHEN t1.year = 24 THEN '01MAY2015:00:00:00'dt
END AS DATETIME FORMAT=DATETIME20.
FROM HAVE
;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Problem - the date returned is today's date. Statement:
CASE WHEN t1.SCHOOL_YEAR_ID = 18 THEN DATETIME('01MAY2015:00:00:00'DT)
END
CASE WHEN t1.SCHOOL_YEAR_ID = 18 THEN DATETIME('01MAY2015:00:00:00'DT)
END
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
DATETIME() function just returns the current date time. Use just the literal or another function such as INPUTN().
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Oops. Removed my DATETIME and reran. Thanks Tom.