You have not specified if you want to track a specific point on the timeline or if you want to track a time-of-day that adjusts as needed if politicians change the rules of your time zone. The first case (like a rocket launch) is simpler, but I get the sense you meant the second case (like booking a medical/dental appointment). Let's cover both.

Fixed point in time

To schedule a rocket launch, the flight engineers study the weather predictions. Once they pick an opportune launch time, that point on the time line is fixed, unchanging. If politicians change the rules for the time zone, the launch does not move. The planned moment remains the same even though the wall clock time in the surrounding area may change from 15:33 to 14:33. The weather, and the rocket, don't care about the time-of-day, they care about a point on the timeline.

If you are tracking specific point on the timeline, use the standard SQL type TIMESTAMP WITH TIME ZONE . In MS SQL Server, that would be the type datetimeoffset . In Java, use Instant in general, but for JDBC database calls, use OffsetDateTime .
| Kind of data | Standard SQL | MS SQL Server | Java |
| ------------ | ------------ | ------------ | ------------ |
| Point on time line | TIMESTAMP WITH TIME ZONE | datetimeoffset | Generally use Instant .In JDBC, use OffsetDateTime . |
| Duration | VARCHAR | varchar | Duration |

Say our rocket launch flight director has determined a launch time of 3:33 PM on January 23rd, 2024 as seen with an offset of zero hours-minutes-seconds from the temporal meridian of UTC. In standard ISO 8601 text, that would be 2024-01-23T15:33:00Z . The Z on the end indicates an offset of zero, and is pronounced “Zulu”.

The java.time classes use ISO 8601 formats by default when parsing/generating text.

Instant launchWindowStart = Instant.parse( "2024-01-23T15:33:00Z" ) ;

And imagine that the launch window is one hour long. We use Duration class to represent that span of time unattached to the timeline.

Duration launchWindowSpan = Duration.ofHours( 1 ) ;

We can determine the end time:

Instant launchWindowEnd = launchWindowStart.plus( launchWindowSpan ) ;

In our database we could store either the start and the span, or the start and the end, or all three (though all three would violate normalization ). For JDBC, adjust the Instant objects into OffsetDateTime as the SQL standard has no concept mapping to Instant .

OffsetDateTime odtLaunchWindowStart = launchWindowStart.atOffset( ZoneOffset.UTC ) ;
OffsetDateTime odtLaunchWindowEnd = launchWindowEnd.atOffset( ZoneOffset.UTC ) ;

myPreparedStatement.setObject( … , odtLaunchWindowStart ) ;
myPreparedStatement.setString( … , launchWindowSpan.toString() ) ;
myPreparedStatement.setObject( … , odtLaunchWindowEnd ) ;

Retrieval.

Instant launchWindowStart = myResultSet.getObject( … , OffsetDateTime.class).toInstant() ;
Duration launchWindowSpan = Duration.parse( myResultSet.getString() ) ;
Instant launchWindowEnd = myResultSet.getObject( … , OffsetDateTime.class).toInstant() ;

Moving point in time

For appointments, you need to store the date & time-of-day separately from the intended time zone. So two columns in your table.

For date & time-of-day in MS SQL Server, use datetime2 type. In Java, LocalDateTime .

For the time zone, store the Continent/Region name of the zone such as Europe/London . In Java, ZoneId .

Record appointments only with a start time, not a stop time. Instead of a stop time, store a duration. The stop time may not be the time-of-day you expect. For example, if an appointment starts at 1 PM but happens to occur during a "spring-ahead" clock cutover, the stop time might be 4 PM rather than the 3 PM you would otherwise expect so a 2-hour meeting could run 1-4 PM.

Neither the SQL Standard nor MS SQL Server offers a data type for duration. So store as text in standard ISO 8601 format. For duration in the database, store as text in standard ISO 8601 format of PnYnMnDTnHnMnS where P marks the beginning, and the T separates the years-months-days from the hours-minutes-seconds. For Java, use Duration object. A duration of two hours is PT2H .
| Kind of data | Standard SQL | MS SQL Server | Java |
| ------------ | ------------ | ------------ | ------------ |
| Date + time-of-day | TIMESTAMP WITHOUT TIME ZONE | datetime2 | LocalDateTime |
| Time zone name | VARCHAR | varchar | ZoneId |
| Duration | VARCHAR | varchar | Duration |

shedule a time between 1pm to 3pm in United Kingdom

As an example, let's book a week out from today.

ZoneId zLondon = ZoneId.of( "Europe/London" ) ;
LocalDate today = LocalDate.now( zLondon ) ;
LocalDate apptDate = today.plusWeeks( 1 ) ;
LocalTime apptTime = LocalTime.of( 13 , 0 ) ;  // 1 PM.
LocalDateTime apptStart = LocalDateTime.of( apptDate , apptTime ) ;
Duration duration = Duration.ofHours( 2 ) ;

Write those to your datetime2 , VARCHAR , and VARCHAR columns.

myPreparedStatement.setObject( … , apptStart ) ;
myPreparedStatement.setString( … , zLondon.toString() ) ;
myPreparedStatement.setString( … , duration.toString() ) ;

Retrieval.

LocalDateTime apptStart = myResultSet.getObject( … , LocalDateTime.class ) ;
ZoneId zLondon = ZoneId.of( myResultSet.getString( … ) ) ;
Duration duration = Duration.parse( myResultSet.getString( … ) ) ;

Be very clear on this crucial point: LocalDateTime & datetime2 do not represent a moment, are not a point on the timeline. They represent merely a date with a time-of-day. They purposely lack any concept of time zone or offset-from-UTC. So their values are inherently ambiguous.

When it comes time to build a schedule, apply the time zone to the date-time. Very important: Do not store this value, use it only to build a schedule dynamically, temporarily, such as for presentation to the user.

ZonedDateTime zdtLondon = appt.atZone( zLondon ) ;

Now we have a moment, a specific point on the timeline.

You can adjust to another time zone.

ZoneId zEdmonton = ZoneId.of( "America/Edmonton" ) ;
ZonedDateTime zdtEdmonton = zdtLondon.atZoneSameInstant( zEdmonton ) ;

Both zdtLondon and zdtEdmonton represent the same simultaneous moment, the same point on the timeline. The one single moment is being viewed through two different lenses, the lens of the wall clock & calendar used by people in England UK versus the lens of the wall clock and calendar used by the people of Alberta Canada.

Spans of time are generally best handled using the Half-Open approach. In Half-Open, the beginning is inclusive while the ending is exclusive. So an appoint from 1 PM to 3 PM starts at the first moment of the hour 13, then runs up to, but does not include, the first moment of the hour 15 (or so, depending on political time-keeping anomalies mentioned above). Tip: In SQL, do not use BETWEEN in your Half-Open queries, as BETWEEN is Fully Closed (both start and end are inclusive).

To represent the whole span of time, the java.time framework does not define any such class. You could define your own. For example:

record SpanOfTimeZoned ( ZonedDateTime start , ZonedDateTime end ) {}

Or more practical:

public record SpanOfTimeZoned( ZonedDateTime start , ZonedDateTime end ) {
    public static SpanOfTimeZoned of ( ZonedDateTime start , Duration duration ) {
        return new SpanOfTimeZoned ( start , start.plus ( duration ) );
    }

    public Duration duration ( ) {
        return Duration.between ( this.start , this.end );
    }
}

Usage:

SpanOfTimeZoned apptSpan = SpanOfTimeZoned.of( zdtEdmonton , duration ) ;

Or, you might find useful adding the ThreeTen-Extra library to your project. That library offers the Interval class, holding a pair of java.time.Instant objects. An Instant represents a moment as seen with an offset of zero hours-minutes-seconds from the temporal meridian of UTC.

Interval apptSpan = Interval.of( zdtEdmonton.toInstant() , duration ) ;

Note that org.threeten.extra.Interval is always in UTC (an offset of zero), not a particular time zone. You can, of course, always apply a time zone to get a ZonedDateTime .

ZonedDateTime apptStartLondon = apptSpan.getStart().atZone( zLondon ) ;

You said:

The clientside I will be using is java to display the start and end date for different time zones.

In your app, you need to collect from the user:

• Date
• Start time
• Time zone
• Duration (or calculate based on user providing an end-time)

From that, you will produce in Java:

• LocalDateTime object
• ZoneId object
• Duration object

Sounds like you want to convert from the user's time zone to your preferred time zone before storing in the database. Be aware that this conversion is risky. We can adjust from Edmonton time to London time, for example. But that adjustment must use the rules for both those zones as they exist at this moment. In the time between this moment and that appointment, if politicians in Alberta, or politicians in England, were to change their time zone rules, our stored appointment will appear to be wrong from the perspective of the person on the other side of the pond. This change by politicians may seem unlikely; history has proved otherwise. Politicians around the world have shown a penchant for twiddling with the time zone rules of their jurisdiction.

LocalDateTime userLdt = LocalDateTime.of( … ) ;
ZoneId userZoneId = ZoneId.of( … ) ;
Duration duration = Duration.of( … ) ;

ZonedDateTime userZdt = userLdt.atZone( userZoneId ) ;
ZonedDateTime zdtLondon = userZdt.atZoneSameInstant( zLondon ) ;
LocalDateTime apptStart = zdtLondon.toLocalDateTime() ;

At this point you can store apptStart , zLondon , and duration as discussed earlier in this Answer.