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

 



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...