convert this datetime string, 9/10/2023 10:00:18 AM, into date variable, format dd/mm/yyyy

[94seanhogan]

How can I convert a datetime string in the format dd/mm/yyyy hh:mm:ss AM into a date variable in the format yyyy-mm-dd

I am currently doing this:

want = datepart(input(have,anydtdtm.));

format want E8601DA10.;

but its coming out in the American format of yyyy-dd-mm for certain dates such as for 9/10/2023 10:00:18 AM is being converted to 2023-10-09, where as I want 2023-09-10.

Oddly enough though, it converts some dates correctly such as 9/4/2023 7:00:01 AM which converts to 2023-04-09.

Anyway to make this consistently output the British format of yyyy-mm-dd

cheers. 

[LinusH]

It sounds like you source datetime have different formats?

Using anydtxxxx formats should be the "last" resort, since mapping can happen randomly.

Analyze your data, and potentially use different informats based on some logic. 

[ballardw · Accepted solution]

Please think about what you say. You say you have data in dd/mm/yyyy hh:mm:ss  layout. So the first value is the day.

Then complain when 9/10/2023 10:00:18 AM is being converted to 2023-10-09. 09 is the DAY. so if you want YYYY-DD-MM then do not use E8601DA format,

E8601DAw. Format

Writes date values by using the ISO 8601 extended notation yyyy-mm-dd.

I cannot replicate this: Oddly enough though, it converts some dates correctly such as 9/4/2023 7:00:01 AM which converts to 2023-04-09.

The ANYDTDTE and other "any date" functions are guessing. Do not allow that. Use something where YOU control the informat. If that initial value is indeed in ddmmyy structure then:

data example;
   x="9/10/2023 10:00:18 AM";
   date = input(scan(x,1,' '),ddmmyy10.);
   format date yymmdd10.;
run;

The above extracts the date part and controls which informat is used to read the date portion.

If you want to display the value in yy/dd/mm order then you must create a custom format to do so.

[94seanhogan]

serves me right for not re-reading my question - easy typo to make, albeit a crucial one. 

yeah, that worked. - cheers. 

[Tom]

People do display date strings in DMY and MDY order.

To avoid that confusion you should display dates in YMD order since NOBODY displays dates in YDM order.

In your case just use DDMMYY informat to convert the date part of your string into a date value.

If you did need to use one of the ANYDT... informats then make sure to set the DATESTYLE system option to let it know what informat to use for values where the day of the month is less than 13.  So if you want ANYDT.. informats to treat 9/10/2023 as the Ninth of October instead of September Tenth then set DATESTYLE=DMY.