Convert Local Time {'09:58:00'} to UTC

Question

I have a requirement to store Local time(10:31:00) to UTC(04:31:09) in the SQL Server.

I have used the below query to get the desired result.
I have combined @VitalReadingDate='2024-01-11' and local time (10:31:00) to get UTC Time (04:31:09), but it returns Local time not converting in UTC

CONVERT(datetime2, 
    CONVERT(varchar, @VitalReadingDate) + ' ' + CONVERT(varchar, @VitalReadingTime)
) AT TIME ZONE 'UTC',

Answer 1

As already mentioned in the comments, you need to also specify your local time zone if you want to convert between time zones.
I know the syntax looks a bit silly but I didn't write the rules - so it's something like this:

select SYSDATETIME() AT TIME ZONE 'Your local time zone' AT TIME ZONE 'Target Time Zone';

To get a list of time zone names you can query the system table called time_zone_info:

SELECT *
FROM sys.time_zone_info;

which will give you a list like this (very partial):

name	current_utc_offset	is_currently_dst
...	...	...
Cape Verde Standard Time	-01:00	0
UTC	+00:00	0
GMT Standard Time	+00:00	0
Greenwich Standard Time	+00:00	0
Sao Tome Standard Time	+00:00	0
Morocco Standard Time	+01:00	1
...	...	...

Also, Based on your code I'm assuming @VitalReadingDate and @VitalReadingTime are variables of types Date and Time respectively - so you should be aware there's a better way to combine them to a DateTime2 value - which is to convert them to their binary values, combine these values and then convert that to DateTime2:

CONVERT(DATETIME2, CONVERT(VARBINARY(6),TimeValue) + CONVERT(BINARY(3),DateValue))

Please note that the order here is critical. The + operator here is concatenation, not addition, so if you do the date before the time you'll get an error:

Conversion failed when converting date and/or time from character string.

For more information, read Jeff Moden's Combine DATE and TIME to DATETIME2 in SQL Server article on SQLServerCentral.

To some up - here's a complete example:

DECLARE @VitalReadingDate Date = '2024-01-11',
        @VitalReadingTime Time = '10:31:00';

SELECT @VitalReadingDate As DateValue
     , @VitalReadingTime As TimeValue
     , CONVERT(DATETIME2, CONVERT(VARBINARY(6),@VitalReadingTime) + CONVERT(BINARY(3),@VitalReadingDate)) As LocalDateTime
     , CONVERT(DATETIME2, CONVERT(VARBINARY(6),@VitalReadingTime) + CONVERT(BINARY(3),@VitalReadingDate))
         AT TIME ZONE 'Central Asia Standard Time' -- assumed* 
         AT TIME ZONE 'UTC' As UTC;

Which results in this:

DateValue	TimeValue	LocalDateTime	UTC
2024-01-11	10:31:00.0000000	2024-01-11 10:31:00.0000000	2024-01-11 04:31:00.0000000 +00:00

*I've based my assumption of your local time zone based on the 6 hours difference from UTC

You can see a live demo on db<>fiddle