TSQL-Working With Date And Time

Here you will learn how to work with the date and time in SQL Server.

select  GETDATE() as dateNow
This returns the current date and time.

select CURRENT_TIMESTAMP as dateNow

This also return the current date and time.

select SYSDATETIME() as dateNow

This return the current date and its more accurate. 

select datename(WEEKDAY, GETDATE()) as weekDay

This return the current weekday from the current date and time

select DATEADD(YEAR, 1, GETDATE()) as nextYear

This allow adding extra year to the current year.

select DATEDIFF(SECOND, '1984-09-07 12:24:08', GETDATE()) as monthsElapsed

The DateDiff  takes the time unit, the early date , the later date and return the months elapsed. 

The DateDiff shows the difference between two dates.

select DATEPART(hour, '2020-01-02 12:20:05') as theHour

This select the hour of the current date and time

Converting from dates to strings

declare @mydate as datetime = '2021-07-27 03:15:04.453'

select 'The date and time is: ' + @mydate

go

declare @mydate as datetime = '2021-07-27 03:15:04.453'

select 'The date and time is: ' + convert(nvarchar(20),@mydate,104) as convertedDate

go

declare @mydate as datetime = '2021-07-27 03:15:04.453'

select cast(@mydate as nvarchar(20)) as castDate

select try_convert(date,'Tuesday, 27 July 2021') as convertedDate

select parse('Tuesday, 27 July 2021' as date) as parsedDate

select format(cast('2021-07-27 03:15:04.453' as datetime),'D') as formattedLongDate

select format(cast('2021-07-27 03:15:04.453' as datetime),'d') as formattedShortDate

select format(cast('2021-07-27 03:15:04.453' as datetime),'dd-MM-yyyy') as formattedBritishDate

select format(cast('2021-07-27 03:15:04.453' as datetime),'D','zh-CN') as formattedInternationalLongDate

Date offsets

declare @myDateOffset as datetimeoffset(2) = '2021-07-27 03:15:04.453 +03:00' 

select @myDateOffset as dateOffset

go

declare @myDate as datetime2 = '2021-07-27 03:15:04.453'

select TODATETIMEOFFSET(@myDate,'+03:00') as dateOffset

select DATETIME2FROMPARTS(2015,06,25,1,2,3,456,3)

select DATETIMEOFFSETFROMPARTS(2015,06,25,1,2,3,456,5,30,3) as dateOffset

select SYSDATETIMEOFFSET() as timeNowWithOffset;

select SYSUTCDATETIME() as timeNowUTC;

declare @myDateOffset as datetimeoffset = '2021-07-27 03:15:04.453 +03:00'

select SWITCHOFFSET(@myDateOffset,'-03:00') as dateOffsetTallinn