|
The following
conversion options are available for sql datetime formats with century (YYYY). 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).
-- SQL Server datetime formats
-- Century date format MM/DD/YYYY usage in a query
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
*/
-- MM/DD/YY date format
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
*/
-- SQL Server cast datetime to string
SELECT stringDateTime=CAST (getdate() as varchar)
--Result: Dec 29 2012 3:47AM
-- SQL Server date/datetime formats
------------
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
------------
/***** SELECTED SQL DATE/DATETIME FORMATS WITH NAMES *****/
-- 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 date format function
------------
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
-- SQL Server date format function test
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
SELECT dbo.fnFormatDate (CURRENT_TIMESTAMP,'YY.MM.DD') -- 12.01.03
GO
------------
------------
-- SQL Server date and time functions overview
------------
-- SQL Server CURRENT_TIMESTAMP function
-- local NYC - EST - Eastern Standard Time zone
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 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 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 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 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 any 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 any 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
------------
-- 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
SELECT getdate(), dateadd(dd,0, datediff(dd,0,getdate())
--Results: 2010-12-25 08:21:24.733 2010-12-25 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 | 
