How to Format Datet/Time

How to Format Datet/Time
Home

Certifications

Cisco

Protocols

Routers

SQL

Security

Telecommunications

Tools

Unix

Web

How to Format Date/Time

Sometimes I can't find a better way to present something then through a series of code/exmaples, so, without much addoo:

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

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

- 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

----

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

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

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

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

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

CREATE FUNCTION fnAge (@BirthDate DATETIME)

RETURNS @Age TABLE(Years INT,

Months INT,

Days INT)

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

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

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

DROP TABLE sqlConvertDateTime

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

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

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

- 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

- SQL Server alter table

ALTER TABLE Employee ALTER COLUMN FullName nvarchar(50) NOT NULL

ALTER TABLE Employee

ADD CONSTRAINT [PK_Employee] PRIMARY KEY (FullName )

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

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

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

- 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

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

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

SELECT ModifiedDate FROM Employee WHERE FullName = -Rob Walters-

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

SELECT BirthDate FROM Employee WHERE FullName = -Rob Walters-

- Result: 19650123

- Perform cleanup action

DROP TABLE Employee

- SQL nocount

SET NOCOUNT OFF;

----

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

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-

- SQL string to datetime fails without validation

SELECT SalesOrderID, OrderDate = CAST (stringOrderDate as datetime)

FROM DateValidation

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

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

----

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

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

----

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

----

- Function for Monday dates calculation

----

USE AdventureWorks2008;

- SQL user-defined function

- SQL scalar function - UDF

CREATE FUNCTION fnMondayDate

(@Year INT,

@Month INT,

@MondayOrdinal INT)

RETURNS DATETIME

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

- 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

----

This page was created in 0.90242 seconds

Comments and Questions
Last modified: June 04 2012.