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


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

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


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


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


No comments:

Post a Comment

Note: only a member of this blog may post a comment.

New Post

Create Store procedures

Store procedure has some similarities with views in the sense that it allows encapsulation. i.e. to retrieve for further, later use a series...