SQL Date Utility

SQLDateUtility

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

You can also use Code like "101","1" to convert date times
Example:
Select CONVERT(varchar, SYSDATETIME(), 1) AS [USA format] // output-> 10/22/2020
SQL server date format and converting it (Various examples)