RealWorldDevelopers
9/19/2016 - 11:36 PM
Many Date Format Options with SQL
Many Date Format Options with SQL
--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