Getting the +/- hour offset from SQL datetimeoffset

Question

I am trying to parse the datetimeoffset. This is from a SQL Server database table's column that was created as datetimeoffset lengh=10, precision=34, scale=7

Example from one of the records: 2024-07-16 22:30:00.0000000 -04:00

In this case, I would like to get back -4 Hours, and also convert this to user's local time.

Unfortunately, I cannot use T-SQL to do this. like (datename(tz, value)

I have the following :

import java.time.ZoneOffset;
import java.time.ZonedDateTime;
import java.time.format.DateTimeFormatter;

public class Main {
    public static void main(String[] args) throws Exception {
        DateTimeFormatter DATE_TIME_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss:SSS z");

        //Date string with zone information
        //String dateString = "08/03/2019T16:20:17:717 UTC+05:30";
        String dateString = "2024-07-16 22:30:00.0000000 -04:00";

        //Instance with given zone
        ZonedDateTime zdtInstanceAtOffset = ZonedDateTime.parse(dateString, DATE_TIME_FORMATTER);

        //Instance in UTC
        ZonedDateTime zdtInstanceAtUTC = zdtInstanceAtOffset.withZoneSameInstant(ZoneOffset.UTC);

        //Formatting to string
        String dateStringInUTC = zdtInstanceAtUTC.format(DATE_TIME_FORMATTER);

        System.out.println("Hello world!");

        System.out.println(zdtInstanceAtOffset);
        System.out.println(zdtInstanceAtUTC);
        System.out.println(dateStringInUTC);

        //Convert ZonedDateTime to instant which is in UTC
        System.out.println(zdtInstanceAtOffset.toInstant());
    }
}

I get this error:

Exception in thread "main" java.time.format.DateTimeParseException: Text '2024-09-17 00:00:00.0000000 -04:00' could not be parsed at index 2

Any suggestions?

Answer 1

g00se posted this answer as a comment:

 DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.n z");
        OffsetDateTime odt = OffsetDateTime.parse("2024-09-17 00:00:00.0000000 -04:00", dtf);

        System.out.println(odt);

        ZoneOffset o = odt.getOffset();
        System.out.printf("Offset is %s%n", o);

        OffsetDateTime odtLocal = odt.withOffsetSameInstant(ZonedDateTime.now().getOffset());

        System.out.printf("As local OffsetDateTime is %s%n", odtLocal);

It works perfectly well. But I should probably not be encouraging this as I don't believe you're forced to work with strings