Date Functions

AZURE Current DateTime

To know the Additional Details of AZURE’s Current DateTime like Year, Month, Day, Hour, minute, Second, MilliSecond, NanoSecond, AM/PM, Month Name, WeekDay Name, Week, Day of Year and Quarter, Refer the below Simple Query-

DECLARE @Current_DateTime DATETIME= SYSDATETIMEOFFSET() AT TIME ZONE 'India Standard Time';

--To know the details of our TimeZone
/*
select [name],current_utc_offset from sys.time_zone_info 
--Use the below filter if we want to search using your time frame 
--	where current_utc_offset='+05:30' 
--Use the below filter if we want to like search using your name of time zone    
	where [name] like '%India%'
*/

SELECT 
@Current_DateTime AS [Current DATETIME],
YEAR(@Current_DateTime) AS [Current Year],
RIGHT('0' + CAST( MONTH(@Current_DateTime) AS VARCHAR(2) ), 2) AS [Current Month],
RIGHT('0' + CAST( DAY(@Current_DateTime) AS VARCHAR(2) ), 2) AS [Current Day of Month],
RIGHT('00' + CAST( DATEPART(HOUR,@Current_DateTime) AS VARCHAR(2) ), 2) AS [Current Hour],
RIGHT('00' + CAST( DATEPART(MINUTE,@Current_DateTime) AS VARCHAR(2) ), 2) AS [Current Minute],
RIGHT('00' + CAST( DATEPART(SECOND,@Current_DateTime) AS VARCHAR(2) ), 2) AS [Current Second],
RIGHT('000' + CAST( DATEPART(MILLISECOND,@Current_DateTime) AS VARCHAR(3) ), 3) AS [Current Millisecond],
RIGHT('000000000' + CAST( DATEPART(NANOSECOND,@Current_DateTime) AS VARCHAR(9) ), 9) AS [Current Nanosecond],
CASE WHEN DATEPART(HOUR, @Current_DateTime) BETWEEN 0 AND 11 THEN 'AM' ELSE 'PM' END AS [AM/PM],
DATENAME(MONTH,@Current_DateTime) AS [Current MonthName],
DATENAME(WEEKDAY,@Current_DateTime) AS [Current WeekDay Name],
DATEPART(DAYOFYEAR, @Current_DateTime) AS [Current Day of Year],
DATEPART(QUARTER,@Current_DateTime) AS [Current Quarter],
DATEPART(WEEK,@Current_DateTime) AS [Current Week]

In the above query, DATEPART() and DATENAME() Functions are used.

  • Both these functions returns a specified datepart of a specified date and works in SQL Server (starting with 2008), Azure SQL Database.
  • The Difference is DATENAME() Function returns the result as a string value(NVARCHAR as Datatype) where as DATEPART() Function returns a integer value(INT as DataType).

Syntax –

  • DATEPART(datepart, date)
  • DATENAME(datepart, date)

datepart Intervals-

datepartAbbreviations
yearyy, yyyy
quarterqq, q
monthmm, m
dayofyeardy, y
daydd, d
weekwk, ww
weekdaydw
hourhh
minutemi, n
secondss, s
millisecondms
microsecondmcs
nanosecondns
TZoffsettz
ISO_WEEKisowk, isoww

On Azure SQL DB, the default Time zone is UTC.

To Find out the different time zones in a query:

SELECT * FROM sys.time_zone_info

We have the option that AZURE SQL DATABASE provided as AT TIME ZONE

DECLARE @Current_DateTime DATETIME= SYSDATETIMEOFFSET() AT TIME ZONE 'India Standard Time';
SELECT @Current_DateTime AS Current_DateTime;

Thanks for reading!

Advertisement

3 thoughts on “AZURE Current DateTime

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s