How to format datetime & date in Sql Server 2005

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/>

About these ads

106 Responses to “How to format datetime & date in Sql Server 2005”

  1. esrom Says:

    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)
    )

    • Anubhav Goyal Says:

      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.

  2. Cory Says:

    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:
    http://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’)

    • Anubhav Goyal Says:

      Thanks Cory, I am sure many will benefit from the modifications you have added.

    • Cory Says:

      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:
      http://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’)

  3. Marcus Says:

    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?

    • Cory Says:

      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

  4. Chris Says:

    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

  5. Sajith Says:

    great and so much helpful content! thanks

  6. Eric Fickes Says:

    Thanks for posting this. I just found out about converting datetime using the numbers

  7. Kusal Says:

    Many Thanks. Great job…..!

  8. ABitSmart Says:

    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.

  9. Geller Says:

    Anubhav,

    Great post… keep going

  10. Digitalweb Says:

    This is a really helpful post for every SQL developer or support engineer. Thanks. Keep up the good work.

  11. v-d1 Says:

    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

    • Adam Says:

      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

  12. Armando Says:

    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))

  13. Gogorichie The Blog » Stupid Date Formatting Says:

    [...] remember the correct expression I did a quick Google search and came across this awesome page http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/ covering exactly what I was looking [...]

  14. Happy David Says:

    Great posting…
    Thanks a lot….
    Terima Kasih Banyak

  15. Murren Says:

    Very helpful. Thank you.

  16. jai Says:

    thanxssssssssssssssss dude… Keep it doing

  17. sl8rz Says:

    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.

  18. bubbles Says:

    very very thanks

    i will use ;)

    Declare @Date1 varchar
    Set @Date1 = CONVERT(varchar, GETDATE(), 112)

  19. Suraj Says:

    Interesting Very Good, Spread Your Knowledge

  20. lukaz1010 Says:

    Splendid. Many thanks !!!

  21. S Says:

    Billions of thanks! Sending good luck your way!!!

  22. Rajat Says:

    @Anubhav,
    This is really a great work. I was looking for experience function I got it in your article in fnage.
    thanks,
    Rajat Bhalla

  23. Joom Says:

    Thank you so much. ^^

  24. developergirl Says:

    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!!!!

  25. venkatesh Pilla Says:

    Helped me a lot . . . . . . .

  26. Nicolas Says:

    Great article! Helped me a lot! Thanks

  27. luong Says:

    How do I get a datetime format: dd / MM

  28. Roy Says:

    Incredible. Thanks!

  29. AymenRM Says:

    Hi All

    i want to convert my DATETIME SQL colum to yyMMddHHmm style

    for exemple : 03/03/2011 07:11:28 to 1103030711

    Thanks

  30. Luís Costa Says:

    Thanks
    Great article!
    Helped me to done my job using only T-SQL…. uff!

  31. sanju yadav Says:

    select DtLeaveFromDate , DtLeaveToDate from TblLeaveTransaction
    where (DtLeaveFromDate > =’02-07-2011′
    and DtLeaveFromDate = ’02-07-2011′ and DtLeaveToDate <='02-21-2011')

  32. akshaya Says:

    how to convert date and time into mm/dd/yyyy 24HH:MM:SS.
    in SQL SERVER

  33. Jijo David Says:

    Very helpful, keep up the good work

  34. temizlik robotu Says:

    Thanks nice post , very helpful

  35. Naresh Vidhani Says:

    Hi,
    I go through your article,nice work,you have nicely represented date time stuff.

  36. Cash for mobile Says:

    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

  37. Boosh Says:

    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.

  38. Azim Says:

    excelent work

  39. Sumant30 Says:

    Thanks For A Good solution!!

  40. Talha Ashfaque Says:

    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

  41. Indresh Says:

    Thanks Anubhav.. We had to fix a datetime issue in production and your page helped alot !

  42. indresh47 Says:

    Thanks alot Anubhav. Your post helped us resolve an issue in production today !

  43. How to format datetime & date in Sql Server 2005 | manjunayak Says:

    [...] Posted in sql server 2005. 50 Comments » [...]

  44. MCTS Exam Says:

    What about SELECT DateFormat(mm-yy,Datefield) FROM TABLE

    ???

  45. List GR Says:

    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?

  46. Zamil Says:

    Very good web page. Have looked it up a number of times.

  47. Divya Says:

    Thanks .. i learnt a lot

  48. Smalls Says:

    A very good web page. I have visited it a number of times..

  49. weeeezzll Says:

    Thanks for posting! Awesome, awesome, awesome!

  50. Louise Harrison Says:

    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.

    • Costa Says:

      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.

  51. Genet Says:

    Very helpful!!! Thanks!

  52. Kuldeep Bansal Says:

    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

  53. debarupa Says:

    Thanks a lot for the detailed post! One of the very few helpful ones available on the net.

  54. davidrendall Says:

    Very helpful reference, thanks very much.

  55. Ramakrishna Says:

    very nice article

  56. Raj Says:

    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.

  57. Appala Raju Ch Says:

    Excellent and easy to understand.

    Thanks and regards

    Raju

  58. Linda P. Says:

    Fantasic – Thank you so much for compiling all this information in one place.

  59. huzefa Says:

    thanks

  60. Lakshmi Narayana Says:

    Excellent post, Thank u very much

    Best Regards,
    Lakshman.

  61. Partha Says:

    its truly awesome piece of article for me.
    Thank You and keep up the good work

  62. Insightful Inuit Says:

    amazing peice would you link to my article please?

  63. duyanhphamkiller Says:

    thanks , it’s very helpfull

  64. Rajendra More Says:

    Thanks from heart…

  65. Fabio Says:

    A very useful article!
    Thanks
    Fabio

  66. Miles Eddy Says:

    Thanks, just what I needed. Not sure where the Trans-Action SQL FORMAT() command when, but thanks for the work around code.

  67. Simon Stewart (@simonstewart) Says:

    Awesome format date function. Thanks!

  68. Hammad Says:

    Excellent work ..Thanks for sharing your knowledge. its very helpful

  69. Bibek Says:

    Excellent post, Thank u very much

  70. Vishal Mali Says:

    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….

  71. MM Says:

    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

  72. Hank Freeman Says:

    This is one of the most useful site on the Web for SQL server, Thanks to all that have contributed.
    FHankFreeman

  73. Diodro Says:

    Thanks this is very useful! i love this site

  74. Peter Says:

    thanks! big help

  75. PDZ Says:

    Excellent. I just bookmarked it and will refer to it as needed. Thanks.

  76. Intekhab uddin Says:

    How to convert the date in sql2008 like example : ‘stf4gMruT5gEB3axjC05Mg==’ as 31/01/2013.
    Please give me revert back.

  77. Raghuram Reddy Gottimukkula Says:

    super! with quick examples on date functions.
    Thanq

  78. harsha Says:

    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

  79. harsha Says:

    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.

  80. Thirumalai Says:

    Thanks.. Supeb with easy examples

  81. http://tinyurl.com Says:

    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

  82. MikeT Says:

    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

  83. boopathi Says:

    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

  84. T-SQL Scripts, Tips and Tricks | Random IT Bytes Says:

    […] How to format datetime & datehttp://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/ […]

  85. Sanjay Says:

    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.

  86. Sparker Says:

    Nice work. v useful

  87. Fourth week at CORE Transport Technologies | Tetsuya's PRJ701 Says:

    […] 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 […]

  88. Change of date format (yyyy-mm-dd) from DB into dd/mm/yyyy Says:

    […] 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 […]


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 )

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 35 other followers

%d bloggers like this: