character to strange date (yyyy-mm-dd-hh-mm-ss -ms ?)

[ger15xxhcker]

Hi!

How can i trasform from this string-> '2019-06-13-15.55.04.392000' to a date?

Thanks a lot!

[PeterClemmensen]

Do you want the date or the datetime?

[ger15xxhcker]

Datetime and i have to filter in the where statement  where date_column < 2000-01-01-00.00.00.000000

[ballardw]

---

@ger15xxhcker wrote:

Datetime and i have to filter in the where statement  where date_column < 2000-01-01-00.00.00.000000

---

SAS restricts how you provide a date, time or datetime literal value.

Date "ddMONyy"d or "ddMONyyyy"d: example "01JAN2001"D  the quotes and the D tell SAS to treat the string as a SAS date value.

Datetime "ddMONyy:hh:mm:ss"dt or "01JAN2001:00:00:00"DT  the quotes plus dt indicate datetime value.

So after you have a SAS datetime value in your date_column you would want

where date_column < "01JAN2001:00:00:00"DT;  

or alternatively

where datepart(date_column) < "01JAN2001"D; 

Personally I consider in suboptimal to call a variable with a time component a "date" and prefer a variable name with "DT" or "datetime" to avoid confusion as to the content of the variable. The SAS Date related functions often have issues when fed datetimes as dates use days for value and datetime uses seconds. So many datetime values exceed the valid domain of the date related functions and formats besides yielding incorrect results (except possibly near midnight on 1Jan1960 which  is the base date for both).

https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/... has a PDF with much information about dates.

[Kurt_Bremser]

To a datetime:

data test;
charts = '2019-06-13-15.55.04.392000';
date = input(substr(charts,1,10),yymmdd10.);
time = input(substr(charts,12),time15.6);
numts = date*86400 + time;
format numts e8601dt26.6;
run;

[novinosrin · Accepted solution]

Hi @ger15xxhcker 

data test;
dt_char= '2019-06-13-15.55.04.392000';
dt_time_num=input(dt_char,ymddttm24.);
format dt_time_num datetime20.;
run;

So basically, the idea is apply the right informat to read nonstandard char datetime value and convert the same  to a numeric datetime value i.e number of seconds from Jan1,1960

[Kurt_Bremser]

Take a length of 26 for the conversion, or you'll lose some of the microseconds. And also use more space and fractional digits in the display format.