In SQL Server you can use the FOR JSON clause in a query to format the results as JSON. When doing this, you must choose either the AUTO or the PATH option. This article contains examples of using the AUTO option.
Category: SQL
List of Date and Time Functions in SQL Server (T-SQL)
This post contains a list of Transact-SQL date and time functions available in SQL Server (as of SQL Server 2017).
This includes functions that return the date or parts of the date, as well as functions that manipulate and/or format the date.
@@DATEFIRST – Get the First Day of the Week in SQL Server
In SQL Server, the first day of the week is determined by the current language settings. You can also override that with the SET DATEFIRST statement, which allows you to explicitly set the first day of the week.
In either case, you can use the @@DATEFIRST function to find out what settings your session is using for the first day of the week. This article demonstrates how.
SET DATEFIRST – Set the First Day of the Week in SQL Server
In SQL Server, you can use SET DATEFIRST to set the first day of the week.
The first day of the week can be different, depending on the language being used. For example the default for us_English is 7 (Sunday), whereas the default for Deutsch (German) is 1 (Monday).
This article demonstrates how to change the first day of the week without changing the language.
YEAR() Examples in SQL Server (T-SQL)
In SQL Server, you can use the YEAR() function to return the “year” part of a date. This is returned as an integer.
Below are examples of how to use this function.
MONTH() Examples in SQL Server (T-SQL)
In SQL Server, you can use the MONTH() function to return the “month” part of a date. This is returned as an integer (not the month name).
Below are examples of how to use this function.
DAY() Examples in SQL Server (T-SQL)
In SQL Server, you can use the DAY() function to return the “day” part of a date. This function returns an integer that represents the day of the month (not the day of the week).
Below are examples of how to use this function.
SYSUTCDATETIME() Examples in SQL Server (T-SQL)
In SQL Server, the T-SQL SYSUTCDATETIME() function is used to return the current UTC time (Coordinated Universal Time). UTC time is the primary time standard by which the world regulates clocks and time.
The return value of the SYSUTCDATETIME() function is derived from the computer on which the instance of SQL Server is running. The time zone is not included, and it is returned as a datetime2 value. The fractional second precision specification has a range from 1 to 7 digits. The default precision is 7 digits.
SYSUTCDATETIME() does the same thing that GETUTCDATE() does, except that it returns a higher fractional precision. As mentioned, SYSUTCDATETIME() returns a datetime2 value, whereas the GETUTCDATE() function returns a datetime value.
GETUTCDATE() Examples in SQL Server (T-SQL)
When using SQL Server, you can use the T-SQL GETUTCDATE() function to return the current UTC time (Coordinated Universal Time). UTC time is the primary time standard by which the world regulates clocks and time.
The return value of the GETUTCDATE() function is derived from the computer on which the instance of SQL Server is running. The time zone is not included, and it is returned as a datetime value.
SYSDATETIMEOFFSET() Examples in SQL Server (T-SQL)
In SQL Server, the transact-sql SYSDATETIMEOFFSET() function returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running. This value includes the time zone offset.
Examples of usage below.