How to convert character to date in PROC SQL

[PhatRam33]

Is there a way to convert a field stored as a character/string and convert it into a date type field?

The database I'm pulling from has all date fields stored as character data types.

Example of how field is stored:

01/07/2016 17:44:32  Need to convert to date type to display as 07JAN2016.

Here was my first atttempt

,input(substr(createtime,1,2) || substr(createtime,4,2) || substr(createtime,7,4), 10.) format=Date9. as TEST_DT

My end result was 30JAN4895 which is not correct =(.

Any help would be greatly appreciated.

[PGStats · Accepted solution]

Try

input(createtime, anydtdtm.) as test_dt format=dtdate9.

[PhatRam33]

Thank you very much this worked perfectly! I have one follow-up question: If I were to pull for say a specific date range instead of creating another proc sql, would I need to do a similar conversion in my where clause?

If so, what would the syntax look like?

My syntax is probably totally off.. I was thinking something along these lines in my where clause:

input(createtime, anydtdtm.) between ('01JAN2016', dtdate9.) and ('31JAN2016', dtdate9.)

[PGStats]

A where clause to select for a date range would look like

where input(createtime, anydtdtm.) between '01JAN2016:00:00:00'dt and '17FEB2016:23:59:59'dt; 

or

where datepart(input(createtime, anydtdtm.)) between '01JAN2016'd and '17FEB2016'd;

[PhatRam33]

Many thanks! Worked like a charm!

[ballardw]

It helps to understand how SAS stores dates and datetime values. Dates are numbers of days since 1 Jan 1960. Datetimes are numbers of seconds so confusion between the data you have and how you treat it can get entertaining. The values are simply numerics so date values may be abused in many ways.

So what you did was 1) create a string value of 1072016 then 2) input it as a number which when Formatted with DATE9 yielded the results you showed.