Assume you’ve been asked to return a date stored as a datetime2 in a specific format using Transact-SQL.
For SQL Server 2012+, one option is to use T-SQL’s FORMAT() function. FORMAT() is used with either a .NET format string or a custom formatting string to determine the formatting that should be applied. Starting with Server 2017, FORMAT() also accepts a culture as its optional third parameter. I’ll cover formatting dates with FORMAT() in a future post.
The other way to return dates as formatted strings is with the CONVERT() function. CONVERT() handles the conversion from datetime2 to varchar or nvarchar . The format of the resulting string is determined by passing a style code as the CONVERT() function’s optional third parameter.
Since the formatting style codes you’ll pass into
CONVERT()aren’t specific to T-SQL, I’d use this method instead ofFORMAT()if portability is a concern.
The query below returns a given date as a string, with each of the formatting styles applied. I keep this query handy and reference it any time I’m asked to return a date in a specific format.
Output from the Example:
Example Query:
BEGIN TRAN DECLARE @COLUMN datetime2 = '2018-07-23 08:47:29.147' SELECT CONVERT(varchar,@COLUMN,0) AS 'Style_0', CONVERT(varchar,@COLUMN,100) AS 'Style_100', CONVERT(varchar,@COLUMN,1) AS 'Style_1', CONVERT(varchar,@COLUMN,101) AS 'Style_101', CONVERT(varchar,@COLUMN,2) AS 'Style_2', CONVERT(varchar,@COLUMN,102) AS 'Style_102', CONVERT(varchar,@COLUMN,3) AS 'Style_3', CONVERT(varchar,@COLUMN,103) AS 'Style_103', CONVERT(varchar,@COLUMN,4) AS 'Style_4', CONVERT(varchar,@COLUMN,104) AS 'Style_104'; SELECT CONVERT(varchar,@COLUMN,5) AS 'Style_5', CONVERT(varchar,@COLUMN,105) AS 'Style_105', CONVERT(varchar,@COLUMN,6) AS 'Style_6', CONVERT(varchar,@COLUMN,106) AS 'Style_106', CONVERT(varchar,@COLUMN,7) AS 'Style_7', CONVERT(varchar,@COLUMN,107) AS 'Style_107', CONVERT(varchar,@COLUMN,8) AS 'Style_8', CONVERT(varchar,@COLUMN,108) AS 'Style_108', CONVERT(varchar,@COLUMN,9) AS 'Style_9', CONVERT(varchar,@COLUMN,109) AS 'Style_109'; SELECT CONVERT(varchar,@COLUMN,10) AS 'Style_10', CONVERT(varchar,@COLUMN,110) AS 'Style_110', CONVERT(varchar,@COLUMN,11) AS 'Style_11', CONVERT(varchar,@COLUMN,111) AS 'Style_111', CONVERT(varchar,@COLUMN,12) AS 'Style_12', CONVERT(varchar,@COLUMN,112) AS 'Style_112', CONVERT(varchar,@COLUMN,13) AS 'Style_13', CONVERT(varchar,@COLUMN,113) AS 'Style_113', CONVERT(varchar,@COLUMN,14) AS 'Style_14', CONVERT(varchar,@COLUMN,114) AS 'Style_114'; SELECT CONVERT(varchar,@COLUMN,20) AS 'Style_20', CONVERT(varchar,@COLUMN,120) AS 'Style_120', CONVERT(varchar,@COLUMN,21) AS 'Style_21', CONVERT(varchar,@COLUMN,121) AS 'Style_121', CONVERT(varchar,@COLUMN,126) AS 'Style_126', CONVERT(varchar,@COLUMN,127) AS 'Style_127', CONVERT(varchar,@COLUMN,130) AS 'Style_130', CONVERT(varchar,@COLUMN,131) AS 'Style_131'; ROLLBACK --- Snippet #ACEAC5A8A112CBEA87676D57F58C3070 (TSQL) --- The latest version of this code is at: --- https://www.sevendaysofschema.com/tsql-examples/date-fromats/
Questions or comments about this script? Be a part of the conversation. It only takes a minute to post a comment.
In this Script
DATEDIFF GETDATE CONVERT SELECT
Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate.
For larger differences, see DATEDIFF_BIG. For an overview of all T-SQL date and time data types and functions, see Date and Time Data Types and Functions.
DATEDIFF ( datepart , startdate , enddate )
/*
datepart boundaries
*/
SELECT DATEDIFF(year, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(quarter, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(month, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(dayofyear, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(day, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(week, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(hour, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(minute, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(second, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(millisecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
/*
A. Specifying columns for startdate and enddate
*/
CREATE TABLE dbo.Duration
(
startDate datetime2
,endDate datetime2
);
INSERT INTO dbo.Duration(startDate,endDate)
VALUES('2007-05-06 12:10:09','2007-05-07 12:10:09');
SELECT DATEDIFF(day,startDate,endDate) AS 'Duration'
FROM dbo.Duration;
-- Returns: 1
/*
B. Specifying user-defined variables for startdate and enddate
*/
DECLARE @startdate datetime2 = '2007-05-05 12:10:09.3312722';
DECLARE @enddate datetime2 = '2007-05-04 12:10:09.3312722';
SELECT DATEDIFF(day, @startdate, @enddate);
/*
C. Specifying scalar system functions for startdate and enddate
*/
SELECT DATEDIFF(millisecond, GETDATE(), SYSDATETIME());
/*
D. Specifying scalar subqueries and scalar functions for startdate and enddate
*/
USE AdventureWorks2012;
GO
SELECT DATEDIFF(day,(SELECT MIN(OrderDate) FROM Sales.SalesOrderHeader),
(SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader));
/*
E. Specifying constants for startdate and enddate
*/
SELECT DATEDIFF(day, '2007-05-07 09:53:01.0376635'
, '2007-05-08 09:53:01.0376635');
/*
F. Specifying numeric expressions and scalar system functions for enddate
*/
USE AdventureWorks2012;
GO
SELECT DATEDIFF(day, '2007-05-07 09:53:01.0376635', GETDATE()+ 1)
AS NumberOfDays
FROM Sales.SalesOrderHeader;
GO
USE AdventureWorks2012;
GO
SELECT DATEDIFF(day, '2007-05-07 09:53:01.0376635', DATEADD(day,1,SYSDATETIME())) AS NumberOfDays
FROM Sales.SalesOrderHeader;
GO
/*
G. Specifying ranking functions for startdate
*/
USE AdventureWorks2012;
GO
SELECT p.FirstName, p.LastName
,DATEDIFF(day,ROW_NUMBER() OVER (ORDER BY
a.PostalCode),SYSDATETIME()) AS 'Row Number'
FROM Sales.SalesPerson s
INNER JOIN Person.Person p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address a
ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0;
/*
H. Specifying an aggregate window function for startdate
*/
USE AdventureWorks2012;
GO
SELECT soh.SalesOrderID, sod.ProductID, sod.OrderQty,soh.OrderDate
,DATEDIFF(day,MIN(soh.OrderDate)
OVER(PARTITION BY soh.SalesOrderID),SYSDATETIME() ) AS 'Total'
FROM Sales.SalesOrderDetail sod
INNER JOIN Sales.SalesOrderHeader soh
ON sod.SalesOrderID = soh.SalesOrderID
WHERE soh.SalesOrderID IN(43659,58918);
GO
/*
I. Specifying columns for startdate and enddate
*/
CREATE TABLE dbo.Duration (
startDate datetime2
,endDate datetime2
);
INSERT INTO dbo.Duration(startDate,endDate)
VALUES('2007-05-06 12:10:09','2007-05-07 12:10:09');
SELECT TOP(1) DATEDIFF(day,startDate,endDate) AS Duration
FROM dbo.Duration;
-- Returns: 1
/*
J. Specifying scalar subqueries and scalar functions for startdate and enddate
*/
-- Uses AdventureWorks
SELECT TOP(1) DATEDIFF(day,(SELECT MIN(HireDate) FROM dbo.DimEmployee),
(SELECT MAX(HireDate) FROM dbo.DimEmployee))
FROM dbo.DimEmployee;
/*
K. Specifying constants for startdate and enddate
*/
-- Uses AdventureWorks
SELECT TOP(1) DATEDIFF(day, '2007-05-07 09:53:01.0376635'
, '2007-05-08 09:53:01.0376635') FROM DimCustomer;
/*
L. Specifying ranking functions for startdate
*/
-- Uses AdventureWorks
SELECT FirstName, LastName
,DATEDIFF(day,ROW_NUMBER() OVER (ORDER BY
DepartmentName),SYSDATETIME()) AS RowNumber
FROM dbo.DimEmployee;
/*
M. Specifying an aggregate window function for startdate
*/
-- Uses AdventureWorks
SELECT FirstName, LastName, DepartmentName
,DATEDIFF(year,MAX(HireDate)
OVER (PARTITION BY DepartmentName),SYSDATETIME()) AS SomeValue
FROM dbo.DimEmployee
Related Topics: CAST and CONVERT DATEDIFF_BIG
Date and Time Data Types and Functions: CURRENT_TIMESTAMP DATEADD DATEDIFF_BIG DATEFROMPARTS DATENAME DATETIME2FROMPARTS DATETIMEFROMPARTS DATETIMEOFFSETFROMPARTS DAY EOMONTH GETDATE GETUTCDATE ISDATE MONTH SET DATEFIRST SET DATEFORMAT SET LANGUAGE SMALLDATETIMEFROMPARTS sp_helplanguage SWITCHOFFSET
Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running.
NOTE: SYSDATETIME and SYSUTCDATETIME have more fractional seconds precision than GETDATE and GETUTCDATE. SYSDATETIMEOFFSET includes the system time zone offset. SYSDATETIME, SYSUTCDATETIME, and SYSDATETIMEOFFSET can be assigned to a variable of any of the date and time types.
For an overview of all T-SQL date and time data types and functions, see Date and Time Data Types and Functions.
GETDATE ( )
/*
Remarks
*/
DECLARE @dt datetimeoffset = switchoffset (CONVERT(datetimeoffset, GETDATE()), '-04:00');
SELECT * FROM t
WHERE c1 > @dt OPTION (RECOMPILE);
/*
A. Getting the current system date and time
*/
SELECT SYSDATETIME()
,SYSDATETIMEOFFSET()
,SYSUTCDATETIME()
,CURRENT_TIMESTAMP
,GETDATE()
,GETUTCDATE();
/*
B. Getting the current system date
*/
SELECT CONVERT (date, SYSDATETIME())
,CONVERT (date, SYSDATETIMEOFFSET())
,CONVERT (date, SYSUTCDATETIME())
,CONVERT (date, CURRENT_TIMESTAMP)
,CONVERT (date, GETDATE())
,CONVERT (date, GETUTCDATE());
/*
C. Getting the current system time
*/
SELECT CONVERT (time, SYSDATETIME())
,CONVERT (time, SYSDATETIMEOFFSET())
,CONVERT (time, SYSUTCDATETIME())
,CONVERT (time, CURRENT_TIMESTAMP)
,CONVERT (time, GETDATE())
,CONVERT (time, GETUTCDATE());
/*
D. Getting the current system date and time
*/
SELECT SYSDATETIME()
,CURRENT_TIMESTAMP
,GETDATE();
/*
E. Getting the current system date
*/
SELECT CONVERT (date, SYSDATETIME())
,CONVERT (date, CURRENT_TIMESTAMP)
,CONVERT (date, GETDATE());
/*
F. Getting the current system time
*/
SELECT CONVERT (time, SYSDATETIME())
,CONVERT (time, CURRENT_TIMESTAMP)
,CONVERT (time, GETDATE());
Related Topics: CAST and CONVERT
Date and Time Data Types and Functions: CURRENT_TIMESTAMP DATEADD DATEDIFF_BIG DATEFROMPARTS DATENAME DATETIME2FROMPARTS DATETIMEFROMPARTS DATETIMEOFFSETFROMPARTS DAY EOMONTH GETUTCDATE ISDATE MONTH SET DATEFIRST SET DATEFORMAT SET LANGUAGE SMALLDATETIMEFROMPARTS sp_helplanguage SWITCHOFFSET
Converts an expression of one data type to another.
For example, the following examples change the input datatype, into two other datatypes, with different levels of precision.
``
/*
CAST and CONVERT (Transact-SQL)
*/
SELECT 9.5 AS Original, CAST(9.5 AS int) AS int,
CAST(9.5 AS decimal(6,4)) AS decimal;
SELECT 9.5 AS Original, CONVERT(int, 9.5) AS int,
CONVERT(decimal(6,4), 9.5) AS decimal;
/*
Truncating and rounding results
*/
DECLARE @myval decimal (5, 2);
SET @myval = 193.57;
SELECT CAST(CAST(@myval AS varbinary(20)) AS decimal(10,5));
-- Or, using CONVERT
SELECT CONVERT(decimal(10,5), CONVERT(varbinary(20), @myval));
/*
Supplementary characters (surrogate pairs)
*/
DECLARE @x NVARCHAR(10) = 'ab' + NCHAR(0x10000);
SELECT CAST (@x AS NVARCHAR(3));
/*
A. Using both CAST and CONVERT
*/
-- Use CAST
USE AdventureWorks2012;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS int) LIKE '3%';
GO
-- Use CONVERT.
USE AdventureWorks2012;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CONVERT(int, ListPrice) LIKE '3%';
GO
/*
B. Using CAST with arithmetic operators
*/
USE AdventureWorks2012;
GO
SELECT CAST(ROUND(SalesYTD/CommissionPCT, 0) AS int) AS Computed
FROM Sales.SalesPerson
WHERE CommissionPCT != 0;
GO
/*
C. Using CAST to concatenate
*/
SELECT 'The list price is ' + CAST(ListPrice AS varchar(12)) AS ListPrice
FROM dbo.DimProduct
WHERE ListPrice BETWEEN 350.00 AND 400.00;
/*
D. Using CAST to produce more readable text
*/
SELECT DISTINCT CAST(EnglishProductName AS char(10)) AS Name, ListPrice
FROM dbo.DimProduct
WHERE EnglishProductName LIKE 'Long-Sleeve Logo Jersey, M';
/*
E. Using CAST with the LIKE clause
*/
USE AdventureWorks2012;
GO
SELECT p.FirstName, p.LastName, s.SalesYTD, s.BusinessEntityID
FROM Person.Person AS p
JOIN Sales.SalesPerson AS s
ON p.BusinessEntityID = s.BusinessEntityID
WHERE CAST(CAST(s.SalesYTD AS int) AS char(20)) LIKE '2%';
GO
/*
F. Using CONVERT or CAST with typed XML
*/
CONVERT(XML, '<root><child/></root>')
/*
G. Using CAST and CONVERT with datetime data
*/
SELECT
GETDATE() AS UnconvertedDateTime,
CAST(GETDATE() AS nvarchar(30)) AS UsingCast,
CONVERT(nvarchar(30), GETDATE(), 126) AS UsingConvertTo_ISO8601 ;
GO
/*
H. Using CONVERT with binary and character data
*/
--Convert the binary value 0x4E616d65 to a character value.
SELECT CONVERT(char(8), 0x4E616d65, 0) AS [Style 0, binary to character];
/*
I. Converting date and time data types
*/
DECLARE @d1 date, @t1 time, @dt1 datetime;
SET @d1 = GETDATE();
SET @t1 = GETDATE();
SET @dt1 = GETDATE();
SET @d1 = GETDATE();
-- When converting date to datetime the minutes portion becomes zero.
SELECT @d1 AS [date], CAST (@d1 AS datetime) AS [date as datetime];
-- When converting time to datetime the date portion becomes zero
-- which converts to January 1, 1900.
SELECT @t1 AS [time], CAST (@t1 AS datetime) AS [time as datetime];
-- When converting datetime to date or time non-applicable portion is dropped.
SELECT @dt1 AS [datetime], CAST (@dt1 AS date) AS [datetime as date],
CAST (@dt1 AS time) AS [datetime as time];
/*
J. Using CAST and CONVERT
*/
SELECT EnglishProductName AS ProductName, ListPrice
FROM dbo.DimProduct
WHERE CAST(ListPrice AS int) LIKE '3%';
/*
K. Using CAST with arithmetic operators
*/
SELECT ProductKey, UnitPrice,UnitPriceDiscountPct,
CAST(ROUND (UnitPrice*UnitPriceDiscountPct,0) AS int) AS DiscountPrice
FROM dbo.FactResellerSales
WHERE SalesOrderNumber = 'SO47355'
AND UnitPriceDiscountPct > .02;
/*
L. Using CAST with the LIKE clause
*/
SELECT EnglishProductName AS Name, ListPrice
FROM dbo.DimProduct
WHERE CAST(CAST(ListPrice AS int) AS char(20)) LIKE '2%';
/*
M. Using CAST and CONVERT with datetime data
*/
SELECT TOP(1)
SYSDATETIME() AS UnconvertedDateTime,
CAST(SYSDATETIME() AS nvarchar(30)) AS UsingCast,
CONVERT(nvarchar(30), SYSDATETIME(), 126) AS UsingConvertTo_ISO8601
FROM dbo.DimCustomer;
Related Topics: Data Type Conversion FORMAT SELECT STR System Functions Write International Transact-SQL Statements
T-SQL Functions: CERT_ID CERTPROPERTY CHOOSE COLLATIONPROPERTY CRYPT_GEN_RANDOM HAS_DBACCESS IIF LOGINPROPERTY PUBLISHINGSERVERNAME SESSIONPROPERTY TERTIARY_WEIGHTS TEXTPTR TEXTVALID USER
Retrieves one or more rows or columns from the database. The UNION, EXCEPT, and INTERSECT operators can be used between queries to combine or compare their results into one result set. <Rick’s Tip> The SELECT syntax used for Azure SQL Data Warehouse and Parallel Data Warehouse differs from that used for SQL Server and Azure SQL Database.
Permissions: Selecting data requires SELECT permission on the table or view, which could be inherited from a higher scope such as SELECT permission on the schema or CONTROL permission on the table. Or requires membership in the db_datareader or db_owner fixed database roles, or the sysadmin fixed server role. Creating a new table using SELECTINTO also requires both the CREATETABLE permission, and the ALTERSCHEMA permission on the schema that owns the new table.
-- Syntax for SQL Server and Azure SQL Database
<SELECT statement> ::=
[ WITH { [ XMLNAMESPACES ,] [ <common_table_expression> [,...n] ] } ]
<query_expression>
[ ORDER BY { order_by_expression | column_position [ ASC | DESC ] }
[ ,...n ] ]
[ <FOR Clause>]
[ OPTION ( <query_hint> [ ,...n ] ) ]
<query_expression> ::=
{ <query_specification> | ( <query_expression> ) }
[ { UNION [ ALL ] | EXCEPT | INTERSECT }
<query_specification> | ( <query_expression> ) [...n ] ]
<query_specification> ::=
SELECT [ ALL | DISTINCT ]
[TOP ( expression ) [PERCENT] [ WITH TIES ] ]
< select_list >
[ INTO new_table ]
[ FROM { <table_source> } [ ,...n ] ]
[ WHERE <search_condition> ]
[ <GROUP BY> ]
[ HAVING < search_condition > ]
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse
[ WITH <common_table_expression> [ ,...n ] ]
SELECT <select_criteria>
[;]
<select_criteria> ::=
[ TOP ( top_expression ) ]
[ ALL | DISTINCT ]
{ * | column_name | expression } [ ,...n ]
[ FROM { table_source } [ ,...n ] ]
[ WHERE <search_condition> ]
[ GROUP BY <group_by_clause> ]
[ HAVING <search_condition> ]
[ ORDER BY <order_by_expression> ]
[ OPTION ( <query_option> [ ,...n ] ) ]
/* A. Using SELECT to retrieve rows and columns */ SELECT * FROM DimEmployee ORDER BY LastName; /* B. Using SELECT with column headings and calculations */ SELECT FirstName, LastName, BaseRate, BaseRate * 40 AS GrossPay FROM DimEmployee ORDER BY LastName; /* C. Using DISTINCT with SELECT */ SELECT DISTINCT Title FROM DimEmployee ORDER BY Title; /* D. Using GROUP BY */ SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales FROM FactInternetSales GROUP BY OrderDateKey ORDER BY OrderDateKey; /* E. Using GROUP BY with multiple groups */ SELECT OrderDateKey, PromotionKey, AVG(SalesAmount) AS AvgSales, SUM(SalesAmount) AS TotalSales FROM FactInternetSales GROUP BY OrderDateKey, PromotionKey ORDER BY OrderDateKey; /* F. Using GROUP BY and WHERE */ SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales FROM FactInternetSales WHERE OrderDateKey > '20020801' GROUP BY OrderDateKey ORDER BY OrderDateKey; /* G. Using GROUP BY with an expression */ SELECT SUM(SalesAmount) AS TotalSales FROM FactInternetSales GROUP BY (OrderDateKey * 10); /* H. Using GROUP BY with ORDER BY */ SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales FROM FactInternetSales GROUP BY OrderDateKey ORDER BY OrderDateKey; /* I. Using the HAVING clause */ SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales FROM FactInternetSales GROUP BY OrderDateKey HAVING OrderDateKey > 20010000 ORDER BY OrderDateKey;
Related Topics: Hints SELECT Examples
T-SQL Query Elements: CONTAINS EXPLAIN FREETEXT FROM GROUP BY HAVING IS NULL PIVOT and UNPIVOT PREDICT READTEXT TOP UPDATE UPDATETEXT WHERE WRITETEXT
About the Archive This script is part of my personal code snippet library, which I’ve been posting to this site using the WordPress automation processes I’m working on.
