Tutorial by Examples

You can use the CONVERT function to cast a datetime datatype to a formatted string. SELECT GETDATE() AS [Result] -- 2016-07-21 07:56:10.927 You can also use some built-in codes to convert into a specific format. Here are the options built into SQL Server: DECLARE @convert_code INT = 100 -- See ...

SQL Server 2012 You can utilize the new function: FORMAT(). Using this you can transform your DATETIME fields to your own custom VARCHAR format. Example DECLARE @Date DATETIME = '2016-09-05 00:01:02.333' SELECT FORMAT(@Date, N'dddd, MMMM dd, yyyy hh:mm:ss tt') Monday, September 05, 2016 ...

The built-in functions GETDATE and GETUTCDATE each return the current date and time without a time zone offset. The return value of both functions is based on the operating system of the computer on which the instance of SQL Server is running. The return value of GETDATE represents the current tim...

General syntax: DATEADD (datepart , number , datetime_expr) To add a time measure, the number must be positive. To subtract a time measure, the number must be negative. Examples DECLARE @now DATETIME2 = GETDATE(); SELECT @now; --2016-07-21 14:39:46.4170000 SELECT DAT...

These are the datepart values available to date & time functions: datepartAbbreviationsyearyy, yyyyquarterqq, qmonthmm, mdayofyeardy, ydaydd, dweekwk, wwweekdaydw, whourhhminutemi, nsecondss, smillisecondmsmicrosecondmcsnanosecondns NOTE: Use of abbreviations is generally discouraged as they c...

General syntax: DATEDIFF (datepart, datetime_expr1, datetime_expr2) It will return a positive number if datetime_expr is in the past relative to datetime_expr2, and a negative number otherwise. Examples DECLARE @now DATETIME2 = GETDATE(); DECLARE @oneYearAgo DATETIME2 = DATEADD(YEAR, -1, @now...

DATEPART returns the specified datepart of the specified datetime expression as a numeric value. DATENAME returns a character string that represents the specified datepart of the specified date. In practice DATENAME is mostly useful for getting the name of the month or the day of the week. There a...

Using the DATEADD and DATEDIFF functions, it's possible to return the last date of a month. SELECT DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, '2016-09-23') + 1, 0)) -- 2016-09-30 00:00:00.000 SQL Server 2012 The EOMONTH function provides a more concise way to return the last date of a month, and...

There are many ways to return a Date from a DateTime object SELECT CONVERT(Date, GETDATE()) SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) returns 2016-07-21 00:00:00.000 SELECT CAST(GETDATE() AS DATE) SELECT CONVERT(CHAR(10),GETDATE(),111) SELECT FORMAT(GETDATE(), 'yyyy-MM-dd') Note th...

This function will take 2 datetime parameters, the DOB, and a date to check the age at CREATE FUNCTION [dbo].[Calc_Age] ( @DOB datetime , @calcDate datetime ) RETURNS int AS BEGIN declare @age int IF (@calcDate < @DOB ) RETURN -1 -- If a DOB is supplied a...

SQL Server 2012 In Transact SQL , you may define an object as Date (or DateTime) using the [DATEFROMPARTS][1] (or [DATETIMEFROMPARTS][1]) function like following: DECLARE @myDate DATE=DATEFROMPARTS(1988,11,28) DECLARE @someMoment DATETIME=DATEFROMPARTS(1988,11,28,10,30,50,123) The parameter...

Date FormatSQL StatementSample OutputYY-MM-DDSELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 20), 8) AS [YY-MM-DD]SELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 11), '/', '-') AS [YY-MM-DD]11-06-08YYYY-MM-DDSELECT CONVERT(VARCHAR(10), SYSDATETIME(), 120) AS [YYYY-MM-DD]SELECT REPLACE(CONVERT(VAR...