How to format datetime & date in Sql Server 2005Now, this was for 2005, but, honestly, I dont think anything has changed for 2008 (and doubt anything will for 2012). Here is the reference (again not mine):
– 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
Thursday, September 15, 2016
0 comments:
Post a Comment