Execute the following Microsoft SQL Server T-SQL datetime and date formatting scripts in Management Studio Query Editor to demonstrate the multitude of temporal data formats available in SQL Server.
First we start with the conversion options available for sql datetime formats with century (YYYY or CCYY format). Subtracting 100 from the Style (format) number will transform dates without century (YY). For example Style 103 is with century, Style 3 is without century. The default Style values – Style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121 – always return the century (yyyy) format.
— Microsoft SQL Server T-SQL date and datetime formats
— Date time formats – mssql datetime
— MSSQL getdate returns current system date and time in standard internal format
SELECT convert(varchar, getdate(), 100) — mon dd yyyy hh:mmAM (or PM)
— Oct 2 2008 11:01AM
SELECT convert(varchar, getdate(), 101) — mm/dd/yyyy – 10/02/2008
SELECT convert(varchar, getdate(), 102) — yyyy.mm.dd – 2008.10.02
SELECT convert(varchar, getdate(), 103) — dd/mm/yyyy
SELECT convert(varchar, getdate(), 104) — dd.mm.yyyy
SELECT convert(varchar, getdate(), 105) — dd-mm-yyyy
SELECT convert(varchar, getdate(), 106) — dd mon yyyy
SELECT convert(varchar, getdate(), 107) — mon dd, yyyy
SELECT convert(varchar, getdate(), 108) — hh:mm:ss
SELECT convert(varchar, getdate(), 109) — mon dd yyyy hh:mm:ss:mmmAM (or PM)
— Oct 2 2008 11:02:44:013AM
SELECT convert(varchar, getdate(), 110) — mm-dd-yyyy
SELECT convert(varchar, getdate(), 111) — yyyy/mm/dd
SELECT convert(varchar, getdate(), 112) — yyyymmdd
SELECT convert(varchar, getdate(), 113) — dd mon yyyy hh:mm:ss:mmm
— 02 Oct 2008 11:02:07:577
SELECT convert(varchar, getdate(), 114) — hh:mm:ss:mmm(24h)
SELECT convert(varchar, getdate(), 120) — yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(varchar, getdate(), 121) — yyyy-mm-dd hh:mm:ss.mmm
SELECT convert(varchar, getdate(), 126) — yyyy-mm-ddThh:mm:ss.mmm
— 2008-10-02T10:52:47.513
— SQL create different date styles with t-sql string functions
SELECT replace(convert(varchar, getdate(), 111), ‘/’, ‘ ‘) — yyyy mm dd
SELECT convert(varchar(7), getdate(), 126) — yyyy-mm
SELECT right(convert(varchar, getdate(), 106), 8) — mon yyyy
————
— SQL Server date formatting function – convert datetime to string
————
— SQL datetime functions
— SQL Server date formats
— T-SQL convert dates
— Formatting dates sql server
CREATE FUNCTION dbo.fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))
RETURNS VARCHAR(32)
AS
BEGIN
DECLARE @StringDate VARCHAR(32)
SET @StringDate = @FormatMask
IF (CHARINDEX (‘YYYY’,@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘YYYY’,
DATENAME(YY, @Datetime))
IF (CHARINDEX (‘YY’,@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘YY’,
RIGHT(DATENAME(YY, @Datetime),2))
IF (CHARINDEX (‘Month’,@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘Month’,
DATENAME(MM, @Datetime))
IF (CHARINDEX (‘MON’,@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)
SET @StringDate = REPLACE(@StringDate, ‘MON’,
LEFT(UPPER(DATENAME(MM, @Datetime)),3))
IF (CHARINDEX (‘Mon’,@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘Mon’,
LEFT(DATENAME(MM, @Datetime),3))
IF (CHARINDEX (‘MM’,@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘MM’,
RIGHT(‘0’+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))
IF (CHARINDEX (‘M’,@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘M’,
CONVERT(VARCHAR,DATEPART(MM, @Datetime)))
IF (CHARINDEX (‘DD’,@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘DD’,
RIGHT(‘0’+DATENAME(DD, @Datetime),2))
IF (CHARINDEX (‘D’,@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘D’,
DATENAME(DD, @Datetime))
RETURN @StringDate
END
GO
— Microsoft SQL Server date format function test
— MSSQL formatting dates
SELECT dbo.fnFormatDate (getdate(), ‘MM/DD/YYYY’) — 01/03/2012
SELECT dbo.fnFormatDate (getdate(), ‘DD/MM/YYYY’) — 03/01/2012
SELECT dbo.fnFormatDate (getdate(), ‘M/DD/YYYY’) — 1/03/2012
SELECT dbo.fnFormatDate (getdate(), ‘M/D/YYYY’) — 1/3/2012
SELECT dbo.fnFormatDate (getdate(), ‘M/D/YY’) — 1/3/12
SELECT dbo.fnFormatDate (getdate(), ‘MM/DD/YY’) — 01/03/12
SELECT dbo.fnFormatDate (getdate(), ‘MON DD, YYYY’) — JAN 03, 2012
SELECT dbo.fnFormatDate (getdate(), ‘Mon DD, YYYY’) — Jan 03, 2012
SELECT dbo.fnFormatDate (getdate(), ‘Month DD, YYYY’) — January 03, 2012
SELECT dbo.fnFormatDate (getdate(), ‘YYYY/MM/DD’) — 2012/01/03
SELECT dbo.fnFormatDate (getdate(), ‘YYYYMMDD’) — 20120103
SELECT dbo.fnFormatDate (getdate(), ‘YYYY-MM-DD’) — 2012-01-03
— CURRENT_TIMESTAMP returns current system date and time in standard internal format
SELECT dbo.fnFormatDate (CURRENT_TIMESTAMP,‘YY.MM.DD’) — 12.01.03
GO
————
/***** SELECTED SQL DATE/DATETIME FORMATS WITH NAMES *****/
— SQL format datetime
— Default format: Oct 23 2006 10:40AM
SELECT [Default]=CONVERT(varchar,GETDATE(),100)
— US-Style format: 10/23/2006
SELECT [US-Style]=CONVERT(char,GETDATE(),101)
— ANSI format: 2006.10.23
SELECT [ANSI]=CONVERT(char,CURRENT_TIMESTAMP,102)
— UK-Style format: 23/10/2006
SELECT [UK-Style]=CONVERT(char,GETDATE(),103)
— German format: 23.10.2006
SELECT [German]=CONVERT(varchar,GETDATE(),104)
— ISO format: 20061023
SELECT ISO=CONVERT(varchar,GETDATE(),112)
— ISO8601 format: 2008-10-23T19:20:16.003
SELECT [ISO8601]=CONVERT(varchar,GETDATE(),126)
————
— SQL Server datetime formats
— Century date format MM/DD/YYYY usage in a query
— Format dates SQL Server 2005
SELECT TOP (1)
SalesOrderID,
OrderDate = CONVERT(char(10), OrderDate, 101),
OrderDateTime = OrderDate
FROM AdventureWorks.Sales.SalesOrderHeader
/* Result
SalesOrderID OrderDate OrderDateTime
43697 07/01/2001 2001-07-01 00:00:00.000
*/
— SQL update datetime column
— SQL datetime DATEADD
UPDATE Production.Product
SET ModifiedDate=DATEADD(dd,1, ModifiedDate)
WHERE ProductID = 1001
— MM/DD/YY date format
— Datetime format sql
SELECT TOP (1)
SalesOrderID,
OrderDate = CONVERT(varchar(8), OrderDate, 1),
OrderDateTime = OrderDate
FROM AdventureWorks.Sales.SalesOrderHeader
ORDER BY SalesOrderID desc
/* Result
SalesOrderID OrderDate OrderDateTime
75123 07/31/04 2004-07-31 00:00:00.000
*/
— Combining different style formats for date & time
— Datetime formats
— Datetime formats sql
DECLARE @Date DATETIME
SET @Date = ‘2015-12-22 03:51 PM’
SELECT CONVERT(CHAR(10),@Date,110) + SUBSTRING(CONVERT(varchar,@Date,0),12,8)
— Result: 12-22-2015 3:51PM
— Microsoft SQL Server cast datetime to string
SELECT stringDateTime=CAST (getdate() as varchar)
— Result: Dec 29 2012 3:47AM
————
— SQL Server date and time functions overview
————
— SQL Server CURRENT_TIMESTAMP function
— SQL Server datetime functions
— local NYC – EST – Eastern Standard Time zone
— SQL DATEADD function – SQL DATEDIFF function
SELECT CURRENT_TIMESTAMP — 2012-01-05 07:02:10.577
— SQL Server DATEADD function
SELECT DATEADD(month,2,‘2012-12-09’) — 2013-02-09 00:00:00.000
— SQL Server DATEDIFF function
SELECT DATEDIFF(day,‘2012-12-09’,‘2013-02-09’) — 62
— SQL Server DATENAME function
SELECT DATENAME(month, ‘2012-12-09’) — December
SELECT DATENAME(weekday, ‘2012-12-09’) — Sunday
— SQL Server DATEPART function
SELECT DATEPART(month, ‘2012-12-09’) — 12
— SQL Server DAY function
SELECT DAY(‘2012-12-09’) — 9
— SQL Server GETDATE function
— local NYC – EST – Eastern Standard Time zone
SELECT GETDATE() — 2012-01-05 07:02:10.577
— SQL Server GETUTCDATE function
— London – Greenwich Mean Time
SELECT GETUTCDATE() — 2012-01-05 12:02:10.577
— SQL Server MONTH function
SELECT MONTH(‘2012-12-09’) — 12
— SQL Server YEAR function
SELECT YEAR(‘2012-12-09’) — 2012
————
— T-SQL Date and time function application
— CURRENT_TIMESTAMP and getdate() are the same in T-SQL
————
— SQL first day of the month
— SQL first date of the month
— SQL first day of current month – 2012-01-01 00:00:00.000
SELECT DATEADD(dd,0,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))
— SQL last day of the month
— SQL last date of the month
— SQL last day of current month – 2012-01-31 00:00:00.000
SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP)+1,0))
— SQL first day of last month
— SQL first day of previous month – 2011-12-01 00:00:00.000
SELECT DATEADD(mm,-1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))
— SQL last day of last month
— SQL last day of previous month – 2011-12-31 00:00:00.000
SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,DATEADD(MM,-1,GETDATE()))+1,0))
— SQL first day of next month – 2012-02-01 00:00:00.000
SELECT DATEADD(mm,1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))
— SQL last day of next month – 2012-02-28 00:00:00.000
SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,DATEADD(MM,1,GETDATE()))+1,0))
GO
— SQL first day of a month – 2012-10-01 00:00:00.000
DECLARE @Date datetime; SET @Date = ‘2012-10-23’
SELECT DATEADD(dd,0,DATEADD(mm, DATEDIFF(mm,0,@Date),0))
GO
— SQL last day of a month – 2012-03-31 00:00:00.000
DECLARE @Date datetime; SET @Date = ‘2012-03-15’
SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,@Date)+1,0))
GO
— SQL first day of year
— SQL first day of the year – 2012-01-01 00:00:00.000
SELECT DATEADD(yy, DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0)
— SQL last day of year
— SQL last day of the year – 2012-12-31 00:00:00.000
SELECT DATEADD(yy,1, DATEADD(dd, –1, DATEADD(yy,
DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0)))
— SQL last day of last year
— SQL last day of previous year – 2011-12-31 00:00:00.000
SELECT DATEADD(dd,-1,DATEADD(yy,DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0))
GO
— SQL calculate age in years, months, days
— SQL table-valued function
— SQL user-defined function – UDF
— SQL Server age calculation – date difference
— Format dates SQL Server 2008
USE AdventureWorks2008;
GO
CREATE FUNCTION fnAge (@BirthDate DATETIME)
RETURNS @Age TABLE(Years INT,
Months INT,
Days INT)
AS
BEGIN
DECLARE @EndDate DATETIME, @Anniversary DATETIME
SET @EndDate = Getdate()
SET @Anniversary = Dateadd(yy,Datediff(yy,@BirthDate,@EndDate),@BirthDate)
INSERT @Age
SELECT Datediff(yy,@BirthDate,@EndDate) – (CASE
WHEN @Anniversary > @EndDate THEN 1
ELSE 0
END), 0, 0
UPDATE @Age SET Months = Month(@EndDate – @Anniversary) – 1
UPDATE @Age SET Days = Day(@EndDate – @Anniversary) – 1
RETURN
END
GO
— Test table-valued UDF
SELECT * FROM fnAge(‘1956-10-23’)
SELECT * FROM dbo.fnAge(‘1956-10-23’)
/* Results
Years Months Days
52 4 1
*/
———-
— SQL date range between
———-
— SQL between dates
USE AdventureWorks;
— SQL between
SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
WHERE OrderDate BETWEEN ‘20040301’ AND ‘20040315’
— Result: 108
— BETWEEN operator is equivalent to >=…AND….<=
SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
WHERE OrderDate
BETWEEN ‘2004-03-01 00:00:00.000’ AND ‘2004-03-15 00:00:00.000’
/*
Orders with OrderDates
‘2004-03-15 00:00:01.000’ – 1 second after midnight (12:00AM)
‘2004-03-15 00:01:00.000’ – 1 minute after midnight
‘2004-03-15 01:00:00.000’ – 1 hour after midnight
are not included in the two queries above.
*/
— To include the entire day of 2004-03-15 use the following two solutions
SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
WHERE OrderDate >= ‘20040301’ AND OrderDate < ‘20040316’
— SQL between with DATE type (SQL Server 2008)
SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
WHERE CONVERT(DATE, OrderDate) BETWEEN ‘20040301’ AND ‘20040315’
———-
— Non-standard format conversion: 2011 December 14
— SQL datetime to string
SELECT [YYYY Month DD] =
CAST(YEAR(GETDATE()) AS VARCHAR(4))+ ‘ ‘+
DATENAME(MM, GETDATE()) + ‘ ‘ +
CAST(DAY(GETDATE()) AS VARCHAR(2))
— Converting datetime to YYYYMMDDHHMMSS format: 20121214172638
SELECT replace(convert(varchar, getdate(),111),‘/’,”) +
replace(convert(varchar, getdate(),108),‘:’,”)
— Datetime custom format conversion to YYYY_MM_DD
select CurrentDate=rtrim(year(getdate())) + ‘_’ +
right(‘0’ + rtrim(month(getdate())),2) + ‘_’ +
right(‘0’ + rtrim(day(getdate())),2)
— Converting seconds to HH:MM:SS format
declare @Seconds int
set @Seconds = 10000
select TimeSpan=right(‘0’ +rtrim(@Seconds / 3600),2) + ‘:’ +
right(‘0’ + rtrim((@Seconds % 3600) / 60),2) + ‘:’ +
right(‘0’ + rtrim(@Seconds % 60),2)
— Result: 02:46:40
— Test result
select 2*3600 + 46*60 + 40
— Result: 10000
— Set the time portion of a datetime value to 00:00:00.000
— SQL strip time from date
— SQL strip time from datetime
SELECT CURRENT_TIMESTAMP ,DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 0)
— Results: 2014-01-23 05:35:52.793 2014-01-23 00:00:00.000
/*******
VALID DATE RANGES FOR DATE/DATETIME DATA TYPES
SMALLDATETIME date range:
January 1, 1900 through June 6, 2079
DATETIME date range:
January 1, 1753 through December 31, 9999
DATETIME2 date range (SQL Server 2008):
January 1,1 AD through December 31, 9999 AD
DATE date range (SQL Server 2008):
January 1, 1 AD through December 31, 9999 AD
*******/
— Selecting with CONVERT into different styles
— Note: Only Japan & ISO styles can be used in ORDER BY
SELECT TOP(1)
Italy = CONVERT(varchar, OrderDate, 105)
, USA = CONVERT(varchar, OrderDate, 110)
, Japan = CONVERT(varchar, OrderDate, 111)
, ISO = CONVERT(varchar, OrderDate, 112)
FROM AdventureWorks.Purchasing.PurchaseOrderHeader
ORDER BY PurchaseOrderID DESC
/* Results
Italy USA Japan ISO
25-07-2004 07-25-2004 2004/07/25 20040725
*/
— SQL Server convert date to integer
DECLARE @Datetime datetime
SET @Datetime = ‘2012-10-23 10:21:05.345’
SELECT DateAsInteger = CAST (CONVERT(varchar,@Datetime,112) as INT)
— Result: 20121023
— SQL Server convert integer to datetime
DECLARE @intDate int
SET @intDate = 20120315
SELECT IntegerToDatetime = CAST(CAST(@intDate as varchar) as datetime)
— Result: 2012-03-15 00:00:00.000
————
— SQL Server CONVERT script applying table INSERT/UPDATE
————
— SQL Server convert date
— Datetime column is converted into date only string column
USE tempdb;
GO
CREATE TABLE sqlConvertDateTime (
DatetimeCol datetime,
DateCol char(8));
INSERT sqlConvertDateTime (DatetimeCol) SELECT GETDATE()
UPDATE sqlConvertDateTime
SET DateCol = CONVERT(char(10), DatetimeCol, 112)
SELECT * FROM sqlConvertDateTime
— SQL Server convert datetime
— The string date column is converted into datetime column
UPDATE sqlConvertDateTime
SET DatetimeCol = CONVERT(Datetime, DateCol, 112)
SELECT * FROM sqlConvertDateTime
— Adding a day to the converted datetime column with DATEADD
UPDATE sqlConvertDateTime
SET DatetimeCol = DATEADD(day, 1, CONVERT(Datetime, DateCol, 112))
SELECT * FROM sqlConvertDateTime
— Equivalent formulation
— SQL Server cast datetime
UPDATE sqlConvertDateTime
SET DatetimeCol = DATEADD(dd, 1, CAST(DateCol AS datetime))
SELECT * FROM sqlConvertDateTime
GO
DROP TABLE sqlConvertDateTime
GO
/* First results
DatetimeCol DateCol
2014-12-25 16:04:15.373 20141225 */
/* Second results:
DatetimeCol DateCol
2014-12-25 00:00:00.000 20141225 */
/* Third results:
DatetimeCol DateCol
2014-12-26 00:00:00.000 20141225 */
————
— SQL month sequence – SQL date sequence generation with table variable
— SQL Server cast string to datetime – SQL Server cast datetime to string
— SQL Server insert default values method
DECLARE @Sequence table (Sequence int identity(1,1))
DECLARE @i int; SET @i = 0
DECLARE @StartDate datetime;
SET @StartDate = CAST(CONVERT(varchar, year(getdate()))+
RIGHT(‘0’+convert(varchar,month(getdate())),2) + ’01’ AS DATETIME)
WHILE ( @i < 120)
BEGIN
INSERT @Sequence DEFAULT VALUES
SET @i = @i + 1
END
SELECT MonthSequence = CAST(DATEADD(month, Sequence,@StartDate) AS varchar)
FROM @Sequence
GO
/* Partial results:
MonthSequence
Jan 1 2012 12:00AM
Feb 1 2012 12:00AM
Mar 1 2012 12:00AM
Apr 1 2012 12:00AM
*/
————
————
— SQL Server Server datetime internal storage
— SQL Server datetime formats
————
— SQL Server datetime to hex
SELECT Now=CURRENT_TIMESTAMP, HexNow=CAST(CURRENT_TIMESTAMP AS BINARY(8))
/* Results
Now HexNow
2009-01-02 17:35:59.297 0x00009B850122092D
*/
— SQL Server date part – left 4 bytes – Days since 1900-01-01
SELECT Now=DATEADD(DAY, CONVERT(INT, 0x00009B85), ‘19000101’)
GO
— Result: 2009-01-02 00:00:00.000
— SQL time part – right 4 bytes – milliseconds since midnight
— 1000/300 is an adjustment factor
— SQL dateadd to Midnight
SELECT Now=DATEADD(MS, (1000.0/300)* CONVERT(BIGINT, 0x0122092D), ‘2009-01-02’)
GO
— Result: 2009-01-02 17:35:59.290
————
————
— String date and datetime date&time columns usage
— SQL Server datetime formats in tables
————
USE tempdb;
SET NOCOUNT ON;
— SQL Server select into table create
SELECT TOP (5)
FullName=convert(nvarchar(50),FirstName+‘ ‘+LastName),
BirthDate = CONVERT(char(8), BirthDate,112),
ModifiedDate = getdate()
INTO Employee
FROM AdventureWorks.HumanResources.Employee e
INNER JOIN AdventureWorks.Person.Contact c
ON c.ContactID = e.ContactID
ORDER BY EmployeeID
GO
— SQL Server alter table
ALTER TABLE Employee ALTER COLUMN FullName nvarchar(50) NOT NULL
GO
ALTER TABLE Employee
ADD CONSTRAINT [PK_Employee] PRIMARY KEY (FullName )
GO
/* Results
Table definition for the Employee table
Note: BirthDate is string date (only)
CREATE TABLE dbo.Employee(
FullName nvarchar(50) NOT NULL PRIMARY KEY,
BirthDate char(8) NULL,
ModifiedDate datetime NOT NULL
)
*/
SELECT * FROM Employee ORDER BY FullName
GO
/* Results
FullName BirthDate ModifiedDate
Guy Gilbert 19720515 2009-01-03 10:10:19.217
Kevin Brown 19770603 2009-01-03 10:10:19.217
Rob Walters 19650123 2009-01-03 10:10:19.217
Roberto Tamburello 19641213 2009-01-03 10:10:19.217
Thierry D’Hers 19490829 2009-01-03 10:10:19.217
*/
— SQL Server age
SELECT FullName, Age = DATEDIFF(YEAR, BirthDate, GETDATE()),
RowMaintenanceDate = CAST (ModifiedDate AS varchar)
FROM Employee ORDER BY FullName
GO
/* Results
FullName Age RowMaintenanceDate
Guy Gilbert 37 Jan 3 2009 10:10AM
Kevin Brown 32 Jan 3 2009 10:10AM
Rob Walters 44 Jan 3 2009 10:10AM
Roberto Tamburello 45 Jan 3 2009 10:10AM
Thierry D’Hers 60 Jan 3 2009 10:10AM
*/
— SQL Server age of Rob Walters on specific dates
— SQL Server string to datetime implicit conversion with DATEADD
SELECT AGE50DATE = DATEADD(YY, 50, ‘19650123’)
GO
— Result: 2015-01-23 00:00:00.000
— SQL Server datetime to string, Italian format for ModifiedDate
— SQL Server string to datetime implicit conversion with DATEDIFF
SELECT FullName,
AgeDEC31 = DATEDIFF(YEAR, BirthDate, ‘20141231’),
AgeJAN01 = DATEDIFF(YEAR, BirthDate, ‘20150101’),
AgeJAN23 = DATEDIFF(YEAR, BirthDate, ‘20150123’),
AgeJAN24 = DATEDIFF(YEAR, BirthDate, ‘20150124’),
ModDate = CONVERT(varchar, ModifiedDate, 105)
FROM Employee
WHERE FullName = ‘Rob Walters’
ORDER BY FullName
GO
/* Results
Important Note: age increments on Jan 1 (not as commonly calculated)
FullName AgeDEC31 AgeJAN01 AgeJAN23 AgeJAN24 ModDate
Rob Walters 49 50 50 50 03-01-2009
*/
————
— SQL combine integer date & time into datetime
————
— Datetime format sql
— SQL stuff
DECLARE @DateTimeAsINT TABLE ( ID int identity(1,1) primary key,
DateAsINT int,
TimeAsINT int
)
— NOTE: leading zeroes in time is for readability only!
INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 235959)
INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 010204)
INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 002350)
INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 000244)
INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 000050)
INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 000006)
SELECT DateAsINT, TimeAsINT,
CONVERT(datetime, CONVERT(varchar(8), DateAsINT) + ‘ ‘+
STUFF(STUFF ( RIGHT(REPLICATE(‘0’, 6) + CONVERT(varchar(6), TimeAsINT), 6),
3, 0, ‘:’), 6, 0, ‘:’)) AS DateTimeValue
FROM @DateTimeAsINT
ORDER BY ID
GO
/* Results
DateAsINT TimeAsINT DateTimeValue
20121023 235959 2012-10-23 23:59:59.000
20121023 10204 2012-10-23 01:02:04.000
20121023 2350 2012-10-23 00:23:50.000
20121023 244 2012-10-23 00:02:44.000
20121023 50 2012-10-23 00:00:50.000
20121023 6 2012-10-23 00:00:06.000
*/
————
— SQL Server string to datetime, implicit conversion with assignment
UPDATE Employee SET ModifiedDate = ‘20150123’
WHERE FullName = ‘Rob Walters’
GO
SELECT ModifiedDate FROM Employee WHERE FullName = ‘Rob Walters’
GO
— Result: 2015-01-23 00:00:00.000
/* SQL string date, assemble string date from datetime parts */
— SQL Server cast string to datetime – sql convert string date
— SQL Server number to varchar conversion
— SQL Server leading zeroes for month and day
— SQL Server right string function
UPDATE Employee SET BirthDate =
CONVERT(char(4),YEAR(CAST(‘1965-01-23’ as DATETIME)))+
RIGHT(‘0’+CONVERT(varchar,MONTH(CAST(‘1965-01-23’ as DATETIME))),2)+
RIGHT(‘0’+CONVERT(varchar,DAY(CAST(‘1965-01-23’ as DATETIME))),2)
WHERE FullName = ‘Rob Walters’
GO
SELECT BirthDate FROM Employee WHERE FullName = ‘Rob Walters’
GO
— Result: 19650123
— Perform cleanup action
DROP TABLE Employee
— SQL nocount
SET NOCOUNT OFF;
GO
————
————
— sql isdate function
————
USE tempdb;
— sql newid – random sort
SELECT top(3) SalesOrderID,
stringOrderDate = CAST (OrderDate AS varchar)
INTO DateValidation
FROM AdventureWorks.Sales.SalesOrderHeader
ORDER BY NEWID()
GO
SELECT * FROM DateValidation
/* Results
SalesOrderID stringOrderDate
56720 Oct 26 2003 12:00AM
73737 Jun 25 2004 12:00AM
70573 May 14 2004 12:00AM
*/
— SQL update with top
UPDATE TOP(1) DateValidation
SET stringOrderDate = ‘Apb 29 2004 12:00AM’
GO
— SQL string to datetime fails without validation
SELECT SalesOrderID, OrderDate = CAST (stringOrderDate as datetime)
FROM DateValidation
GO
/* Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an
out-of-range value.
*/
— sql isdate – filter for valid dates
SELECT SalesOrderID, OrderDate = CAST (stringOrderDate as datetime)
FROM DateValidation
WHERE ISDATE(stringOrderDate) = 1
GO
/* Results
SalesOrderID OrderDate
73737 2004-06-25 00:00:00.000
70573 2004-05-14 00:00:00.000
*/
— SQL drop table
DROP TABLE DateValidation
Go
————
— SELECT between two specified dates – assumption TIME part is 00:00:00.000
————
— SQL datetime between
— SQL select between two dates
SELECT EmployeeID, RateChangeDate
FROM AdventureWorks.HumanResources.EmployeePayHistory
WHERE RateChangeDate >= ‘1997-11-01’ AND
RateChangeDate < DATEADD(dd,1,‘1998-01-05’)
GO
/* Results
EmployeeID RateChangeDate
3 1997-12-12 00:00:00.000
4 1998-01-05 00:00:00.000
*/
/* Equivalent to
— SQL datetime range
SELECT EmployeeID, RateChangeDate
FROM AdventureWorks.HumanResources.EmployeePayHistory
WHERE RateChangeDate >= ‘1997-11-01 00:00:00’ AND
RateChangeDate < ‘1998-01-06 00:00:00’
GO
*/
————
— SQL datetime language setting
— SQL Nondeterministic function usage – result varies with language settings
SET LANGUAGE ‘us_english’; –– Jan 12 2015 12:00AM
SELECT US = convert(VARCHAR,convert(DATETIME,’01/12/2015′));
SET LANGUAGE ‘British’; –– Dec 1 2015 12:00AM
SELECT UK = convert(VARCHAR,convert(DATETIME,’01/12/2015′));
SET LANGUAGE ‘German’; –– Dez 1 2015 12:00AM
SET LANGUAGE ‘Deutsch’; –– Dez 1 2015 12:00AM
SELECT Germany = convert(VARCHAR,convert(DATETIME,’01/12/2015′));
SET LANGUAGE ‘French’; –– déc 1 2015 12:00AM
SELECT France = convert(VARCHAR,convert(DATETIME,’01/12/2015′));
SET LANGUAGE ‘Spanish’; –– Dic 1 2015 12:00AM
SELECT Spain = convert(VARCHAR,convert(DATETIME,’01/12/2015′));
SET LANGUAGE ‘Hungarian’; –– jan 12 2015 12:00AM
SELECT Hungary = convert(VARCHAR,convert(DATETIME,’01/12/2015′));
SET LANGUAGE ‘us_english’;
GO
————
————
— Function for Monday dates calculation
————
USE AdventureWorks2008;
GO
— SQL user-defined function
— SQL scalar function – UDF
CREATE FUNCTION fnMondayDate
(@Year INT,
@Month INT,
@MondayOrdinal INT)
RETURNS DATETIME
AS
BEGIN
DECLARE @FirstDayOfMonth CHAR(10),
@SeedDate CHAR(10)
SET @FirstDayOfMonth = convert(VARCHAR,@Year) + ‘-‘ + convert(VARCHAR,@Month) + ‘-01’
SET @SeedDate = ‘1900-01-01’
RETURN DATEADD(DD,DATEDIFF(DD,@SeedDate,DATEADD(DD,(@MondayOrdinal * 7) – 1,
@FirstDayOfMonth)) / 7 * 7, @SeedDate)
END
GO
— Test Datetime UDF
— Third Monday in Feb, 2015
SELECT dbo.fnMondayDate(2016,2,3)
— 2015-02-16 00:00:00.000
— First Monday of current month
SELECT dbo.fnMondayDate(Year(getdate()),Month(getdate()),1)
— 2009-02-02 00:00:00.000
————
reference <http://www.sqlusa.com/bestpractices2005/centurydateformat/>
Anubhav Goyal 
June 15, 2009 at 4:39 pm
I want a short cut to create a table which has a date column which have in a format I wanted. For example I want my column to be in the format like dd/mm/year e.g 01/12/2009.
can you help me in creating a table which has a column in the form I specified above?
I want you to give me sql syntax in the form below
create table table name
(name char(20),
pdate datetime formated like(dd/mm/yyyy)
)
June 17, 2009 at 3:12 am
I don’t think what you are trying to achieve is possible using datetime. DateTime is sql server datatype and sql server reserves the right to save it in the format it wants to. However what you can do is instruct your application to read the date and convert it in which ever format you want it to display. Other option can be to use varchar to save the date instead of using datetime and then you can insert/ update the date in the format you want.
June 17, 2009 at 3:24 pm
Hey Anubhav,
This is a great post, thanks. I took your fnFormatDate function and added some functionality to it, namely time formatting. I hope the comment box doesn’t mess it up too much.
—
— Formats a datetime according to a mask you pass in.
—
— DateTime you want formatted
— String mask of how you want the date to look (MM/DD/YYYY hh:mm:ss ampm)
— String representation of the formatted date/time
ALTER FUNCTION [dbo].[FormatDate] (
@Datetime DATETIME
,@FormatMask VARCHAR(64)
)
RETURNS VARCHAR(64)
AS
BEGIN
–Expanded on the function shown here:
— https://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/
–“COLLATE SQL_Latin1_General_CP1_CS_AS” tells SQL to be case sensative
DECLARE
@StringDate VARCHAR(64)
,@Month VARCHAR(12)
,@MON VARCHAR(3)
SET @StringDate = @FormatMask
–Special Codes (Codes that return more letters)
—————————————————————————————————
IF (CHARINDEX (‘Month’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘Month’, ‘|Q|’)
IF (CHARINDEX (‘MON’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘MON’, ‘|E|’)
IF (CHARINDEX (‘AMPM’, @StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)
SET @StringDate = REPLACE(@StringDate, ‘AMPM’ COLLATE SQL_Latin1_General_CP1_CS_AS, ‘|X|’)
IF (CHARINDEX (‘ampm’, @StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)
SET @StringDate = REPLACE(@StringDate, ‘ampm’ COLLATE SQL_Latin1_General_CP1_CS_AS, ‘|x|’)
–DATE
—————————————————————————————————
IF (CHARINDEX (‘YYYY’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘YYYY’, DATENAME(YY, @Datetime))
IF (CHARINDEX (‘YY’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘YY’, RIGHT(DATENAME(YY, @Datetime),2))
IF (CHARINDEX (‘MM’, @StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)
SET @StringDate = REPLACE(@StringDate, ‘MM’ COLLATE SQL_Latin1_General_CP1_CS_AS, RIGHT(‘0’+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))
IF (CHARINDEX (‘M’, @StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)
SET @StringDate = REPLACE(@StringDate, ‘M’ COLLATE SQL_Latin1_General_CP1_CS_AS, CONVERT(VARCHAR,DATEPART(MM, @Datetime)))
IF (CHARINDEX (‘DD’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘DD’, RIGHT(‘0’+DATENAME(DD, @Datetime),2))
IF (CHARINDEX (‘D’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘D’, DATENAME(DD, @Datetime))
–TIME
—————————————————————————————————
IF (CHARINDEX (‘hh’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘hh’, RIGHT(‘0’+CONVERT(VARCHAR,DATEPART(HH, @Datetime)),2))
IF (CHARINDEX (‘h’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘h’, DATEPART(HH, @Datetime))
IF (CHARINDEX (‘mm’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘mm’, RIGHT(‘0’+CONVERT(VARCHAR,DATEPART(mi, @Datetime)),2))
IF (CHARINDEX (‘m’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘m’, DATEPART(mi, @Datetime))
IF (CHARINDEX (‘ss’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘ss’, RIGHT(‘0’+CONVERT(VARCHAR,DATEPART(ss, @Datetime)),2))
IF (CHARINDEX (‘s’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘s’, DATEPART(ss, @Datetime))
–Special Codes
— Must be done last because they replace the code with letters that could be seen as another code (‘m’)
—————————————————————————————————
IF (CHARINDEX (‘|Q|’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘|Q|’, DATENAME(MM, @Datetime))
IF (CHARINDEX (‘|E|’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘|E|’, LEFT(DATENAME(MM, @Datetime),3))
IF (CHARINDEX (‘|X|’, @StringDate) > 0)
BEGIN
DECLARE @AMPM VARCHAR(2)
IF DATEPART(hour, @Datetime) > 12
SET @AMPM = ‘pm’
ELSE
SET @AMPM = ‘am’
IF CHARINDEX (‘|X|’, @StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0
SET @AMPM = UPPER(@AMPM)
SET @StringDate = REPLACE(@StringDate, ‘|X|’, @AMPM)
END
RETURN @StringDate
END
GO
SELECT [dbo].[FormatDate] (‘3/1/09 8:15:45 AM’, ‘Month MON MM/DD/YYYY @ hh:mm:ss ampm’)
June 18, 2009 at 4:21 am
Thanks Cory, I am sure many will benefit from the modifications you have added.
June 18, 2009 at 1:19 pm
I made a small adjustment to the function. It now formats the hour smartly based on whether ‘ampm’ is used. Before it would always use the 24 hour based time.
ALTER FUNCTION [dbo].[BZSFormatDate] (
@Datetime DATETIME
,@FormatMask VARCHAR(64)
)
RETURNS VARCHAR(64)
AS
BEGIN
–Expanded on the function shown here:
— https://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/
–“COLLATE SQL_Latin1_General_CP1_CS_AS” tells SQL to be case sensative
DECLARE
@StringDate VARCHAR(64)
,@Month VARCHAR(12)
,@MON VARCHAR(3)
,@24H BIT
SET @StringDate = @FormatMask
SET @24H = 1
–Special Codes (Codes that return more letters)
—————————————————————————————————
IF (CHARINDEX (‘Month’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘Month’, ‘|Q|’)
IF (CHARINDEX (‘MON’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘MON’, ‘|E|’)
IF (CHARINDEX (‘AMPM’, @StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)
BEGIN
SET @StringDate = REPLACE(@StringDate, ‘AMPM’ COLLATE SQL_Latin1_General_CP1_CS_AS, ‘|X|’)
SET @24H = 0
END
IF (CHARINDEX (‘ampm’, @StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)
BEGIN
SET @StringDate = REPLACE(@StringDate, ‘ampm’ COLLATE SQL_Latin1_General_CP1_CS_AS, ‘|x|’)
SET @24H = 0
END
–DATE
—————————————————————————————————
IF (CHARINDEX (‘YYYY’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘YYYY’, DATENAME(YY, @Datetime))
IF (CHARINDEX (‘YY’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘YY’, RIGHT(DATENAME(YY, @Datetime), 2))
IF (CHARINDEX (‘MM’, @StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)
SET @StringDate = REPLACE(@StringDate, ‘MM’ COLLATE SQL_Latin1_General_CP1_CS_AS, RIGHT(‘0’ + CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))
IF (CHARINDEX (‘M’, @StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0)
SET @StringDate = REPLACE(@StringDate, ‘M’ COLLATE SQL_Latin1_General_CP1_CS_AS, CONVERT(VARCHAR, DATEPART(MM, @Datetime)))
IF (CHARINDEX (‘DD’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘DD’, RIGHT(‘0’ + DATENAME(DD, @Datetime),2))
IF (CHARINDEX (‘D’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘D’, DATENAME(DD, @Datetime))
–TIME
—————————————————————————————————
IF (CHARINDEX (‘h’, @StringDate) > 0)
BEGIN
DECLARE @Hour INT; SET @Hour = DATEPART(HH, @Datetime)
SELECT @Hour = CASE WHEN @24H = 0 THEN CASE WHEN @Hour > 12 THEN @Hour – 12 ELSE @Hour END ELSE @Hour END
IF (CHARINDEX (‘hh’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘hh’, RIGHT(‘0’ + CONVERT(VARCHAR, @Hour), 2))
IF (CHARINDEX (‘h’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘h’, @Hour)
END
IF (CHARINDEX (‘mm’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘mm’, RIGHT(‘0’ + CONVERT(VARCHAR, DATEPART(mi, @Datetime)), 2))
IF (CHARINDEX (‘m’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘m’, DATEPART(mi, @Datetime))
IF (CHARINDEX (‘ss’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘ss’, RIGHT(‘0’+CONVERT(VARCHAR,DATEPART(ss, @Datetime)),2))
IF (CHARINDEX (‘s’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘s’, DATEPART(ss, @Datetime))
–Special Codes
— Must be done last because they replace the code with letters that could be seen as another code (‘m’)
—————————————————————————————————
IF (CHARINDEX (‘|Q|’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘|Q|’, DATENAME(MM, @Datetime))
IF (CHARINDEX (‘|E|’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘|E|’, LEFT(DATENAME(MM, @Datetime),3))
IF (CHARINDEX (‘|X|’, @StringDate) > 0)
BEGIN
DECLARE @AMPM VARCHAR(2)
IF DATEPART(HH, @Datetime) > 12
SET @AMPM = ‘pm’
ELSE
SET @AMPM = ‘am’
IF CHARINDEX (‘|X|’, @StringDate COLLATE SQL_Latin1_General_CP1_CS_AS) > 0
SET @AMPM = UPPER(@AMPM)
SET @StringDate = REPLACE(@StringDate, ‘|X|’, @AMPM)
END
RETURN @StringDate
END
GO
SELECT [dbo].[BZSFormatDate] (‘1/31/09 22:15:45:222’, ‘Month MON MM/DD/YYYY @ hh:mm:ss ampm’)
August 20, 2009 at 3:46 pm
I’ve tried to use the fnFormatDate function, but it’s returning some odd results. When using “MON”, it will replace the D in DEC with the day (12-12EC-2009) and the M in MAR and MAY with the month (23-5AY-2009). I thought it might be an issue with which order those elements of the function code appear, but any shuffling of those elements only seemed to make the matter worse. Any ideas on what might be causing this how to remedy it?
September 2, 2009 at 12:59 pm
Marcus,
Have you tried my version of FormatDate? It’s in the comments directly above here. Make sure you get my second post as I made some improvements to it.
Let us know if that works for you.
Cory
September 14, 2009 at 11:34 am
Is it possible to create a function to emulate the work of the DATE datatype in SQL2008.
In essence if my datetime is ‘01.01.1900 00:00:00.000’ I would like to convert it to be ‘01.01.1900’ otherwise I would like to display the date in it’s full datetime format
Many Thanks
Chris
November 12, 2009 at 7:18 am
great and so much helpful content! thanks
December 3, 2009 at 1:26 am
Thanks for posting this. I just found out about converting datetime using the numbers
December 29, 2009 at 11:42 am
Many Thanks. Great job…..!
January 4, 2010 at 3:02 am
Anubhav,
This page of your blog is a reference source for me every time I need to get a DateTime format.
Thank you very much.
February 2, 2010 at 8:39 am
Anubhav,
Great post… keep going
February 8, 2010 at 2:46 pm
This is a really helpful post for every SQL developer or support engineer. Thanks. Keep up the good work.
February 18, 2010 at 2:00 pm
Guys,
Calculating the age from their birth date as of , gives me great problem and i am stuck, although i got already the function on how to calculate their age in Year Format, they required me to compute it with this kind of format
09Y05M02D
That means 09 years, 05 months and 2 days
——————-function to calcculate the age with YEAR format
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER FUNCTION [dbo].[fn_CalculateAge]
(
@BirthDate datetime,
@CurrentDate datetime
)
RETURNS int
AS
BEGIN
IF @BirthDate > @CurrentDate
RETURN 0
DECLARE @Age int
SELECT @Age = DATEDIFF(YY, @BirthDate, @CurrentDate) –
CASE WHEN(
(MONTH(@BirthDate)*100 + DAY(@BirthDate)) >
(MONTH(@CurrentDate)*100 + DAY(@CurrentDate))
) THEN 1 ELSE 0 END
RETURN @Age
END
————————————
how could i make that. please help me
June 18, 2013 at 2:38 pm
This works to return age in the required ’09Y05M02D’ format…
ALTER FUNCTION [dbo].[fnAge]
(
@BirthDate DATETIME
)
RETURNS VARCHAR(9)
AS
BEGIN
DECLARE @Result VARCHAR(9)
,@Anni DATETIME
,@EndDate DATETIME
SET @EndDate = GETDATE()
SET @Anni = DATEADD(yy,DATEDIFF(yy,@BirthDate,@EndDate),@BirthDate)
SELECT @Result =
Right(‘0’ + CONVERT(VARCHAR,DATEDIFF(yy,@BirthDate,@EndDate) –
(CASE
WHEN @Anni > @EndDate THEN 1
ELSE 0
END)),2) + ‘Y’
+ Right(‘0’ + CONVERT(VARCHAR,Month(@EndDate – @Anni) – 1),2) + ‘M’
+ Right(‘0’ + CONVERT(VARCHAR,Day(@EndDate – @Anni) – 1),2) + ‘D’
RETURN @Result
END
March 11, 2010 at 7:05 pm
Wonderful and useful function thanks, my contribution to the code … Miliseconds usage (nnn or n)
Please add this lines before “–Special Codes” section
IF (CHARINDEX (‘nnn’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘nnn’, RIGHT(‘0’+CONVERT(VARCHAR,DATEPART(Ms, @Datetime)),3))
IF (CHARINDEX (‘n’, @StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘n’, DATEPART(Ms, @Datetime))
March 19, 2010 at 7:34 pm
[…] remember the correct expression I did a quick Google search and came across this awesome page https://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/ covering exactly what I was looking […]
April 29, 2010 at 5:40 pm
Great posting…
Thanks a lot….
Terima Kasih Banyak
May 7, 2010 at 7:21 pm
Very helpful. Thank you.
June 4, 2010 at 7:10 am
thanxssssssssssssssss dude… Keep it doing
August 19, 2010 at 11:32 pm
The following code, shown above, is a BIG problem. I’ve been using it for a while now but have not ran into the issue till now. Note that when 12:15pm is used (or any time within an our after 12 noon, the function will return AM instead of PM.
BEGIN
DECLARE @AMPM VARCHAR(2)
IF DATEPART(HH, @Datetime) > 12
SET @AMPM = ‘pm’
ELSE
SET @AMPM = ‘am’
Changing the ” > 12 ” –to– ” > 11 ” will fix the issue.
August 28, 2010 at 1:04 am
very very thanks
i will use 😉
Declare @Date1 varchar
Set @Date1 = CONVERT(varchar, GETDATE(), 112)
September 3, 2010 at 6:36 am
Interesting Very Good, Spread Your Knowledge
September 14, 2010 at 11:35 pm
Splendid. Many thanks !!!
September 17, 2010 at 6:32 pm
Billions of thanks! Sending good luck your way!!!
September 28, 2010 at 9:17 am
@Anubhav,
This is really a great work. I was looking for experience function I got it in your article in fnage.
thanks,
Rajat Bhalla
October 12, 2010 at 6:34 am
Thank you so much. ^^
November 4, 2010 at 7:39 pm
thanks for this info. I believe i have finally found the page i need to really format my dates now. This has everything i need. THANK YOU!!!!
October 23, 2012 at 10:40 am
ys u r right…
November 9, 2010 at 5:04 pm
[…] Here Did you like this? Share […]
December 14, 2010 at 10:22 am
Helped me a lot . . . . . . .
January 4, 2011 at 7:34 am
gd
January 20, 2011 at 9:56 am
Great article! Helped me a lot! Thanks
February 10, 2011 at 11:26 pm
[…] here for useful functions to format […]
February 23, 2011 at 3:03 am
[…] https://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/ […]
April 4, 2011 at 9:04 am
How do I get a datetime format: dd / MM
April 5, 2011 at 5:49 pm
Incredible. Thanks!
April 12, 2011 at 9:07 pm
Hi All
i want to convert my DATETIME SQL colum to yyMMddHHmm style
for exemple : 03/03/2011 07:11:28 to 1103030711
Thanks
April 21, 2011 at 11:05 am
Thanks
Great article!
Helped me to done my job using only T-SQL…. uff!
April 22, 2011 at 10:27 am
select DtLeaveFromDate , DtLeaveToDate from TblLeaveTransaction
where (DtLeaveFromDate > =’02-07-2011′
and DtLeaveFromDate = ’02-07-2011′ and DtLeaveToDate <='02-21-2011')
April 25, 2011 at 10:35 am
how to convert date and time into mm/dd/yyyy 24HH:MM:SS.
in SQL SERVER
May 13, 2011 at 12:12 am
Very helpful, keep up the good work
May 26, 2011 at 2:28 pm
Thanks nice post , very helpful
May 26, 2011 at 5:47 pm
Hi,
I go through your article,nice work,you have nicely represented date time stuff.
May 27, 2011 at 9:34 am
Wow! thanks so much. Date/Times in Sql have always been a pain for me. UK vs US/formatting etc. Why M$ don’t just give a method FormatDate(@date, “”) I’ll never know
June 11, 2011 at 9:26 pm
hello,
i have a datetime variable named date and i want to save it to sql database.
when i save it, it is no longer 10/06/2011 (june, ten), it is become 06/10/2011 (october, six)
what an i doing wrong? i am using c#
this is how i get the date:
string date = DateTime.Now.ToString(“dd/MM/yyyy”);
also this is my first time trying to create a program, please explaine as much as you can.
thanks a lot, Boosh.
June 23, 2011 at 10:25 am
excelent work
June 24, 2011 at 11:37 am
Thanks For A Good solution!!
July 16, 2011 at 6:50 am
Excellent post.
For a complete chart for SQL Server Datetime formats, check this:
http://www.codeshode.com/2011/06/sql-server-datetime-format-chart.html
August 2, 2011 at 9:24 am
Thanks Anubhav.. We had to fix a datetime issue in production and your page helped alot !
August 2, 2011 at 9:28 am
Thanks alot Anubhav. Your post helped us resolve an issue in production today !
August 18, 2011 at 5:37 am
[…] Posted in sql server 2005. 50 Comments » […]
August 25, 2011 at 7:42 am
What about SELECT DateFormat(mm-yy,Datefield) FROM TABLE
???
September 19, 2011 at 3:46 pm
Hello i have an application that stores date and time in as a string field in an sql 2008 table. The application stores the date and time according to the regional settings of the pc that is running and we can’t change this behavor. The problem is that some pcs have to be in UK date format with 12h time (eg. 22/10/2011 1:22:35 pm) some with UK date format with 24h time (eg. 22/10/2011 13:22:25) and some have to be US date format (eg. 10/22/2011 1:22:35 pm) and (eg. 10/22/2011 13:22:25). Is there any automatic way to change the string every time it changing/added to the table to UK 24h format so it will be always the same format in the database? Can it be done using some trigger on update or insert? is there any build in function that already does that? Even a script to run it from time to time may be do the job.
I’m thinking to break apart the string to day, month , year, hour, minute, second , ampm and the put the day and month part in the dd/mm order and somehow change the hour part to 24h if pm and get rid off the “am” and “pm” and then put the modified date/time back to the table.
For example the table has (ignore the dots i put them for formating only)
id……..datesting…………………………value
1………15/10/2011 11:55:01 pm……BLAHBLAH
2………15/10/2011 13:12:20…………BLAKBLAK
3………10/15/2011 6:00:01 pm……..SOMESTUFF
4………10/15/2011 20:16:43…………SOMEOTHERSTUFF
and we want to be
id……..datesting…………………………value
1………15/10/2011 23:55:01…………BLAHBLAH
2………15/10/2011 13:12:20…………BLAKBLAK
3………10/15/2011 18:00:01…………SOMESTUFF
4………10/15/2011 20:16:43…………SOMEOTHERSTUFF
We can display the date parts (day,month,year) correctly using the datepart function but with the time part we have problems because it changes to many ways.
Maybe I’m thinking it with very complicated way of doing it, or not?
January 7, 2012 at 7:52 am
I got So much information thanks a lot…
September 30, 2011 at 1:15 am
Very good web page. Have looked it up a number of times.
October 24, 2011 at 7:27 am
Thanks .. i learnt a lot
November 12, 2011 at 6:46 pm
[…] […]
November 17, 2011 at 7:51 am
A very good web page. I have visited it a number of times..
December 5, 2011 at 7:47 pm
Thanks for posting! Awesome, awesome, awesome!
December 8, 2011 at 12:39 pm
[…] […]
December 13, 2011 at 7:40 pm
Could you add one more section to your Date Format? I need to know how to calculate how many years, months and days an employee has been employed from a hired date and today’s date.
January 4, 2012 at 10:42 am
Hi, i think you need to use the function DATEDIFF ( datepart , startdate , enddate ), MONTH(date) and DAY(date )
the function DATEDIFF counts the “dateparts” that exist form startdate to enddate.
you will need to know:
datepart Abbreviations
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
hour hh
minute mi, n
second ss, s
millisecond ms
microsecond mcs
nanosecond ns
exemple:
table1 (id_emp, date)
So if you want return the year plus the remaining months and days i think you can do like this
select
CASE YEAR([date]) WHEN YEAR(getdate()) THEN 0 ELSE DATEDIFF ( yy , [date] , getdate() )-1 END [Years], MONTH([date]) [Months],
DAY([date]) [Days]
FROM table1
I hope thit is you want.
You could use DATEPART ( datepart , date ) as well.
December 19, 2011 at 3:48 pm
Very helpful!!! Thanks!
December 23, 2011 at 3:15 pm
This is best one article so far I have read online. I would like to appreciate you for making it very simple and easy. I have found another nice post related to this post over the internet which also explained very well. For more details you may check it by visiting this url.
http://mindstick.com/Articles/b08c8553-60a2-4f35-a58f-60c538c7d136/?%E2%80%98SELECT%E2%80%99%20Command%20with%20Date%20Method
Its also helped me lot to complete my task.
Thanks
January 20, 2012 at 7:38 pm
Thanks a lot for the detailed post! One of the very few helpful ones available on the net.
February 8, 2012 at 10:57 am
Very helpful reference, thanks very much.
February 20, 2012 at 9:39 am
very nice article
February 21, 2012 at 2:55 am
Thanks a lot for very nice and superb article. No need to search any other place datetime related query. Its resovle all doubts on one place.
February 28, 2012 at 5:15 am
Excellent and easy to understand.
Thanks and regards
Raju
March 6, 2012 at 2:50 pm
Fantasic – Thank you so much for compiling all this information in one place.
March 15, 2012 at 4:53 am
thanks
April 19, 2012 at 6:37 am
Excellent post, Thank u very much
Best Regards,
Lakshman.
June 1, 2012 at 6:44 am
its truly awesome piece of article for me.
Thank You and keep up the good work
July 1, 2012 at 12:32 pm
amazing peice would you link to my article please?
July 6, 2012 at 8:40 am
thanks , it’s very helpfull
July 23, 2012 at 12:59 pm
Thanks from heart…
July 25, 2012 at 1:39 pm
A very useful article!
Thanks
Fabio
August 8, 2012 at 5:19 pm
Thanks, just what I needed. Not sure where the Trans-Action SQL FORMAT() command when, but thanks for the work around code.
September 7, 2012 at 2:49 pm
Awesome format date function. Thanks!
September 14, 2012 at 11:04 pm
Excellent work ..Thanks for sharing your knowledge. its very helpful
October 5, 2012 at 7:47 am
Excellent post, Thank u very much
October 11, 2012 at 12:31 pm
Sir, the format 101 and 103 are working vice versa in sql server 2005… I have done practice 100 times … please confirm and mail me please sir….
October 26, 2012 at 1:22 pm
How to optimize this below query using datetime format to achieve better performance?
DECLARE @CID as varchar(30),@RunDate as datetime
Set @RunDate = getdate()
Set @CID = convert(varchar(30),@RunDate,121)
Set @CID = replace(@CID,’ ‘,”)
Set @CID = replace(@CID,’:’,”)
Set @CID = replace(@CID,’-‘,”)
Set @CID = replace(@CID,’.’,”)
print @CID
November 26, 2012 at 7:09 pm
This is one of the most useful site on the Web for SQL server, Thanks to all that have contributed.
FHankFreeman
January 9, 2013 at 9:47 am
Thanks this is very useful! i love this site
January 9, 2013 at 9:50 am
thanks! big help
January 9, 2013 at 4:56 pm
Excellent. I just bookmarked it and will refer to it as needed. Thanks.
January 17, 2013 at 11:11 am
How to convert the date in sql2008 like example : ‘stf4gMruT5gEB3axjC05Mg==’ as 31/01/2013.
Please give me revert back.
February 5, 2013 at 1:15 pm
super! with quick examples on date functions.
Thanq
March 11, 2013 at 11:01 am
IF i GIVE pass any date through parameter
the given date should be inserted into table like this 12 Mar 2011
EX:- 21/02/2011
how to convert into given format
March 11, 2013 at 11:03 am
ALTER PROC [dbo].[DT](@D VARCHAR(50))
AS
BEGIN
SET NOCOUNT ON
DECLARE @K VARCHAR(100),@ReturnCode INT,@A VARCHAR(100)
SET @ReturnCode =-2
SET @A=CONVERT(VARCHAR(50),@D,12)
SET @K=CONVERT(VARCHAR(100), CAST(@A AS DATETIME),6)
IF @K=@K
INSERT INTO DateInfo(DateFor) VALUES(@K)
ELSE
SELECT @ReturnCode
END
EXEC DT ’19/09/2011′
if am giving this format 19/02/2011
here 19 is day
02 is month
But it raised error
Msg 242, Level 16, State 3, Line 5
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
March 18, 2013 at 6:55 am
Thanks.. Supeb with easy examples
April 10, 2013 at 5:28 am
[…] https://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/ […]
April 14, 2013 at 2:43 am
I actually was exploring for ideas for my website and discovered your post,
“How to format datetime & date in Sql Server 2005 | Anubhav
Goyal”, do you really mind in the event that I really use many of your
ideas? With thanks ,Ginger
April 24, 2013 at 3:14 pm
worth mentioning that the Age using datediff(YEAR … Calculations don’t calculate age, only the difference between the years of the 2 dates to get a more realistic age you need to datediff(dd… then divide by 365.25 or to cascade with month and day datediffs
May 17, 2013 at 10:33 am
I have a datetime ‘2009-06-24 09:52:43’, which I need to get to a DateTime column of a table.
But I don’t care about the time, just want to get it as apply where condition date only
May 29, 2013 at 6:02 pm
[…] How to format datetime & datehttps://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/ […]
June 13, 2013 at 4:36 pm
dbo.fnFormatDate(Dateadd(dd, -( Day(Dateadd(mm, 1, sd.Doc_Date)) ),Dateadd(mm, 1, sd.Doc_Date)),’Mon YY’)
where sd.Doc_date is a december or march date. – give funny results.
July 31, 2013 at 11:09 am
wow. great.
July 31, 2013 at 2:52 pm
Nice work. v useful
August 9, 2013 at 11:30 pm
[…] https://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/ […]
August 19, 2013 at 12:21 pm
[…] Time separately into date picker and time textbox as string data type. Luckily I found quite good Website for Date Time Conversion, and eventually I succeeded to populate the Vender Number related vender […]
September 24, 2013 at 4:15 pm
[…] , who provided this information to the SQL community on June 11, 2009 – You rock Anubhav! https://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005//*——————————————————————————————-*/–SQL […]
April 2, 2014 at 6:13 pm
[…] What type of SQL server do you have? MySQL: Change MYSQL date format – Stack Overflow MSSQL: How to format datetime & date in Sql Server 2005 | Anubhav Goyal […]
July 6, 2014 at 6:10 am
[…] hello sir, see below link http://www.csharp-examples.net/string-format-datetime/ https://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/ […]
July 9, 2014 at 2:16 am
[…] How to format datetime & date in Sql Server 2005 […]
October 24, 2014 at 3:06 am
nice share… many thaks 🙂
November 2, 2014 at 1:34 pm
Thank You, dear it help alot.
Thanks
January 8, 2015 at 7:45 pm
[…] Her du brug for andre formater kan du se en mere omfattende liste her […]
January 21, 2015 at 10:20 am
Very useful.. a good compilation of Date formats
January 27, 2015 at 6:17 am
very use full
February 4, 2015 at 6:25 am
Hi, I have time in MSSQL DB in 5 digits e.g. 57662 is 16:01:02 and 60802 is 16:53:22. How do I convert the five digits to time? BR, Mikko
March 11, 2015 at 2:07 pm
this is just awesome
March 18, 2015 at 10:34 am
[…] https://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/ […]
November 23, 2015 at 11:00 am
—
— SQL Server date formatting function – convert datetime to string
—
–— SQL datetime functions
–— SQL Server date formats
–— T-SQL convert dates
–— Formatting dates sql server
alter FUNCTION dbo.fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))
RETURNS VARCHAR(32)
AS
BEGIN
DECLARE @StringDate VARCHAR(32)
SET @StringDate = @FormatMask
IF (CHARINDEX (‘YYYY’,@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘YYYY’,
DATENAME(YY, @Datetime))
else IF (CHARINDEX (‘YY’,@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘YY’,
RIGHT(DATENAME(YY, @Datetime),2))
IF (CHARINDEX (‘Month’,@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘Month’,
DATENAME(MM, @Datetime))
else IF (CHARINDEX (‘MON’,@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)
SET @StringDate = REPLACE(@StringDate, ‘MON’,
LEFT(UPPER(DATENAME(MM, @Datetime)),3))
else IF (CHARINDEX (‘Mon’,@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘Mon’,
LEFT(DATENAME(MM, @Datetime),3))
else IF (CHARINDEX (‘MM’,@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘MM’,
RIGHT(‘0’+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))
else IF (CHARINDEX (‘M’,@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘M’,
CONVERT(VARCHAR,DATEPART(MM, @Datetime)))
IF (CHARINDEX (‘DD’,@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘DD’,
RIGHT(‘0’+DATENAME(DD, @Datetime),2))
else IF (CHARINDEX (‘D’,@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘D’,
DATENAME(DD, @Datetime))
RETURN @StringDate
END
GO
January 10, 2016 at 12:40 am
[…] Resources * Table listing all SQL Server DateTime to VarChar formats * A Comprehensive article on SQL Server DateTime formats […]
January 10, 2016 at 3:18 am
[…] * Table listing all SQL Server DateTime to VarChar formats * A Comprehensive article on SQL Server DateTime formats * Useful SQL Date Functions from […]
February 10, 2016 at 7:39 pm
Great post, but you have a logic error for SQL 2008 +. If you use a string like “Month DD, YYYY” and the month you are using has the letter m in it, you will get a number everywhere the month name has an m. I.e. “09-28-2015” would convert to Septe9ber 28, 2015 in your function. I believe that the If statements should be nested checking for the longest match first, so
If (CHARINDEX (‘Month’,@StringDate) > 0) then … else if (CHARINDEX (‘MON’,@StringDate) > 0) and so on
May 11, 2016 at 4:21 am
[…] How to format datetime & date in Sql Server 2005 | … – Jun 11, 2009 · Anubhav Goyal Says: June 17, 2009 at 3:12 am. I don’t think what you are trying to achieve is possible using datetime. DateTime is sql server datatype and … […]
February 16, 2017 at 7:41 am
Good article.. Thanks !
January 31, 2018 at 2:18 pm
Thumbs up to all of you guys … great work!
October 22, 2018 at 5:02 pm
Thanks!
August 20, 2019 at 4:57 pm
[…] https://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/ […]