mssql server 2014, 2016 timezone

• AT TIME ZONE Function

AT TIME ZONE, SWITCHOFFSET, TODATETIMEOFFSET

• AT TIME ZONE 함수는 2016 이후 사용
• 2014 이전은 SWITCHOFFSET, TODATETIMEOFFSET 이용한다.

-- SQL Server timezone 확인
DECLARE @TimeZone VARCHAR(50)
EXEC MASTER.dbo.xp_regread 'HKEY_LOCAL_MACHINE','SYSTEM\CurrentControlSet\Control\TimeZoneInformation','TimeZoneKeyName',@TimeZone OUT
SELECT @TimeZone AS 'serverTimeZone'

-- 쿼리로 확인
SELECT B_IDX, B_TITLE, B_REGDATE FROM dbo.BOARD_FREE ORDER BY B_IDX ASC;
SELECT B_IDX, B_TITLE, B_REGDATE, SWITCHOFFSET(B_REGDATE, '-00:00') AS B_REGDATE_2 FROM BOARD_FREE ORDER BY B_IDX ASC;
SELECT B_IDX, B_TITLE, B_REGDATE, SWITCHOFFSET(B_REGDATE, '-09:00') AS B_REGDATE_2 FROM BOARD_FREE ORDER BY B_IDX ASC;
SELECT B_IDX, B_TITLE, B_REGDATE, SWITCHOFFSET(B_REGDATE, '+09:00') AS B_REGDATE_2 FROM BOARD_FREE ORDER BY B_IDX ASC;
SELECT B_IDX, B_TITLE, B_REGDATE, SWITCHOFFSET(SWITCHOFFSET(B_REGDATE, '-00:00'), '-00:00') AS B_REGDATE_2 FROM BOARD_FREE ORDER BY B_IDX ASC;
SELECT B_IDX, B_TITLE, B_REGDATE, SWITCHOFFSET(SWITCHOFFSET(B_REGDATE, '-09:00'), '-00:00') AS B_REGDATE_2 FROM BOARD_FREE ORDER BY B_IDX ASC;
SELECT B_IDX, B_TITLE, B_REGDATE, SWITCHOFFSET(SWITCHOFFSET(B_REGDATE, '+09:00'), '-00:00') AS B_REGDATE_2 FROM BOARD_FREE ORDER BY B_IDX ASC;
SELECT B_IDX, B_TITLE, B_REGDATE, SWITCHOFFSET(TODATETIMEOFFSET(B_REGDATE, '-00:00'), '-00:00') AS B_REGDATE_2 FROM BOARD_FREE ORDER BY B_IDX ASC;
SELECT B_IDX, B_TITLE, B_REGDATE, SWITCHOFFSET(TODATETIMEOFFSET(B_REGDATE, '-09:00'), '-00:00') AS B_REGDATE_2 FROM BOARD_FREE ORDER BY B_IDX ASC;
SELECT B_IDX, B_TITLE, B_REGDATE, SWITCHOFFSET(TODATETIMEOFFSET(B_REGDATE, '+09:00'), '-00:00') AS B_REGDATE_2 FROM BOARD_FREE ORDER BY B_IDX ASC;

SELECT B_IDX, B_TITLE, B_REGDATE, TODATETIMEOFFSET(B_REGDATE, '-00:00') AS B_REGDATE_2 FROM dbo.BOARD_FREE ORDER BY B_IDX ASC;
SELECT B_IDX, B_TITLE, B_REGDATE, TODATETIMEOFFSET(B_REGDATE, '-09:00') AS B_REGDATE_2 FROM dbo.BOARD_FREE ORDER BY B_IDX ASC;
SELECT B_IDX, B_TITLE, B_REGDATE, TODATETIMEOFFSET(B_REGDATE, '+09:00') AS B_REGDATE_2 FROM dbo.BOARD_FREE ORDER BY B_IDX ASC;
SELECT B_IDX, B_TITLE, B_REGDATE, TODATETIMEOFFSET(SWITCHOFFSET(B_REGDATE, '-00:00'), '-00:00') AS B_REGDATE_2 FROM BOARD_FREE ORDER BY B_IDX ASC;
SELECT B_IDX, B_TITLE, B_REGDATE, TODATETIMEOFFSET(SWITCHOFFSET(B_REGDATE, '-09:00'), '-00:00') AS B_REGDATE_2 FROM BOARD_FREE ORDER BY B_IDX ASC;
SELECT B_IDX, B_TITLE, B_REGDATE, TODATETIMEOFFSET(SWITCHOFFSET(B_REGDATE, '+09:00'), '-00:00') AS B_REGDATE_2 FROM BOARD_FREE ORDER BY B_IDX ASC;
SELECT B_IDX, B_TITLE, B_REGDATE, TODATETIMEOFFSET(TODATETIMEOFFSET(B_REGDATE, '-00:00'), '-00:00') AS B_REGDATE_2 FROM BOARD_FREE ORDER BY B_IDX ASC;
SELECT B_IDX, B_TITLE, B_REGDATE, TODATETIMEOFFSET(TODATETIMEOFFSET(B_REGDATE, '-09:00'), '-00:00') AS B_REGDATE_2 FROM BOARD_FREE ORDER BY B_IDX ASC;
SELECT B_IDX, B_TITLE, B_REGDATE, TODATETIMEOFFSET(TODATETIMEOFFSET(B_REGDATE, '+09:00'), '-00:00') AS B_REGDATE_2 FROM BOARD_FREE ORDER BY B_IDX ASC;

-- sql server 가 Asia/Seoul 이라면 Asia/Seoul +09:00 시간대를 표시
SELECT B_IDX, B_TITLE, TODATETIMEOFFSET(B_REGDATE, '+09:00') AS B_REGDATE FROM dbo.BOARD_FREE ORDER BY B_IDX ASC;
-- Asia/Seoul +09:00 시간을 UTC 시간으로 변경
SELECT B_IDX, B_TITLE, TODATETIMEOFFSET(B_REGDATE, '+09:00') AS B_REGDATE, SWITCHOFFSET(TODATETIMEOFFSET(B_REGDATE, '+09:00'), '-00:00') AS B_REGDATE_2 FROM BOARD_FREE ORDER BY B_IDX ASC;