Skip to content

Instantly share code, notes, and snippets.

@thoroc

thoroc/sqlFormatDateTime.sql

Last active August 29, 2015 14:07
Show Gist options
  • Save thoroc/feee525c2e3ba90bf078 to your computer and use it in GitHub Desktop.
Save thoroc/feee525c2e3ba90bf078 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
sqlFormatDateTime.sql
/*
* @source: http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/
* 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 */
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment