Convert varchar variable to datetime format

[jayvisw11]

Hi,

I have a date variable (eventtime) in my table in varchar format Eg:2021-10-18T16:33:30.388148Z. how can i convert the data type from varchar to utc date? Thank you! 

[ballardw]

VARCHAR where? Basic SAS data sets only have Character and Numeric? Is this in an external database? FedSQL?

Create a new variable using input with the correct informat.

There are Informats  E8601DZw.d and E8601LZw.d, depending on whether you want a standard timezone or local for the Z,

I suspect you might want:

newvar = input (yourunamedvariable,E8601dz27.6);

Assign a desired format, possibly one of the E8601 flavors, for people to understand.

Is the time actually accurate to a millionth of a second???

[Tom]

---

@ballardw wrote:

...

Is the time actually accurate to a millionth of a second???

---

Not if the dates are after the middle of 1988.

40    data test;
41      dt=constant('exactint')/10E6 ;
42      put dt=comma32.6 /
43          dt=datetime27.6
44      ;
45    run;

dt=900,719,925.474099
dt=16JUL1988:23:58:45.474099

[Kurt_Bremser]

On z/OS, it works because of the extra 4 bits of the mantissa.

[jayvisw11]

Hi,

Thank you for your reply. It is table created using FedSQL from an external database. E8601dz27.6 is the format of the value.

data casuser.reporting_table_1;
set casuser.reporting_table_1;
eventtime_1 = input(eventtime,E8601dz27.6);
format eventtime_1 E8601DT.;
drop eventtime;
rename eventtime_1 = eventtime;
run;


When I ran this code, I still see that the eventtime variable data type is double instead of a datatime format.

[Tom]

Does CAS support data types that SAS does not support?  In regular SAS datasets there are only two data types.  Fixed length character strings and floating point numbers. The format attached to a variable just determines how the values are displayed as text, not the type of variable the values are stored in. 

DATETIME values are just floating point numbers which are interpreted as the number of seconds since 1960.  

[ballardw]

---

@jayvisw11 wrote:
Hi,

Thank you for your reply. It is table created using FedSQL from an external database. E8601dz27.6 is the format of the value.

data casuser.reporting_table_1;
set casuser.reporting_table_1;
eventtime_1 = input(eventtime,E8601dz27.6);
format eventtime_1 E8601DT.;
drop eventtime;
rename eventtime_1 = eventtime;
run;


When I ran this code, I still see that the eventtime variable data type is double instead of a datatime format.

---

The question would be does the formatted value look correct? The default length for the format you chose is 19 so the fractional seconds are not displayed. If the value is acceptable then your golden. Just remember your values will contain fractional seconds if you use them in comparisons and may yield the occasional unexpected result depending on what you compare them to.