- 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 formatSELECT 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/2008SELECT convert(varchar, getdate(), 102) - yyyy.mm.dd - 2008.10.02SELECT convert(varchar, getdate(), 103) - dd/mm/yyyySELECT convert(varchar, getdate(), 104) - dd.mm.yyyySELECT convert(varchar, getdate(), 105) - dd-mm-yyyySELECT convert(varchar, getdate(), 106) - dd mon yyyySELECT convert(varchar, getdate(), 107) - mon dd, yyyySELECT convert(varchar, getdate(), 108) - hh:mm:ssSELECT 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-yyyySELECT convert(varchar, getdate(), 111) - yyyy/mm/ddSELECT convert(varchar, getdate(), 112) - yyyymmddSELECT convert(varchar, getdate(), 113) - dd mon yyyy hh:mm:ss:mmm - 02 Oct 2008 11:02:07:577SELECT 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.mmmSELECT 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 functionsSELECT replace(convert(varchar, getdate(), 111), -/-, - -) - yyyy mm ddSELECT convert(varchar(7), getdate(), 126) - yyyy-mmSELECT 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 serverCREATE FUNCTION dbo.fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))RETURNS VARCHAR(32)ASBEGIN 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 @StringDateENDGO- Microsoft SQL Server date format function test- MSSQL formatting datesSELECT dbo.fnFormatDate (getdate(), -MM/DD/YYYY-) - 01/03/2012SELECT dbo.fnFormatDate (getdate(), -DD/MM/YYYY-) - 03/01/2012SELECT dbo.fnFormatDate (getdate(), -M/DD/YYYY-) - 1/03/2012SELECT dbo.fnFormatDate (getdate(), -M/D/YYYY-) - 1/3/2012SELECT dbo.fnFormatDate (getdate(), -M/D/YY-) - 1/3/12SELECT dbo.fnFormatDate (getdate(), -MM/DD/YY-) - 01/03/12SELECT dbo.fnFormatDate (getdate(), -MON DD, YYYY-) - JAN 03, 2012SELECT dbo.fnFormatDate (getdate(), -Mon DD, YYYY-) - Jan 03, 2012SELECT dbo.fnFormatDate (getdate(), -Month DD, YYYY-) - January 03, 2012SELECT dbo.fnFormatDate (getdate(), -YYYY/MM/DD-) - 2012/01/03SELECT dbo.fnFormatDate (getdate(), -YYYYMMDD-) - 20120103SELECT dbo.fnFormatDate (getdate(), -YYYY-MM-DD-) - 2012-01-03- CURRENT_TIMESTAMP returns current system date and time in standard internal formatSELECT dbo.fnFormatDate (CURRENT_TIMESTAMP,-YY.MM.DD-) - 12.01.03GO----/***** SELECTED SQL DATE/DATETIME FORMATS WITH NAMES *****/- SQL format datetime- Default format: Oct 23 2006 10:40AMSELECT [Default]=CONVERT(varchar,GETDATE(),100)- US-Style format: 10/23/2006SELECT [US-Style]=CONVERT(char,GETDATE(),101)- ANSI format: 2006.10.23SELECT [ANSI]=CONVERT(char,CURRENT_TIMESTAMP,102)- UK-Style format: 23/10/2006SELECT [UK-Style]=CONVERT(char,GETDATE(),103)- German format: 23.10.2006SELECT [German]=CONVERT(varchar,GETDATE(),104)- ISO format: 20061023SELECT ISO=CONVERT(varchar,GETDATE(),112)- ISO8601 format: 2008-10-23T19:20:16.003SELECT [ISO8601]=CONVERT(varchar,GETDATE(),126)----- SQL Server datetime formats- Century date format MM/DD/YYYY usage in a query- Format dates SQL Server 2005SELECT TOP (1) SalesOrderID, OrderDate = CONVERT(char(10), OrderDate, 101), OrderDateTime = OrderDateFROM AdventureWorks.Sales.SalesOrderHeader/* ResultSalesOrderID OrderDate OrderDateTime43697 07/01/2001 2001-07-01 00:00:00.000*/- SQL update datetime column- SQL datetime DATEADDUPDATE Production.ProductSET ModifiedDate=DATEADD(dd,1, ModifiedDate)WHERE ProductID = 1001- MM/DD/YY date format- Datetime format sqlSELECT TOP (1) SalesOrderID, OrderDate = CONVERT(varchar(8), OrderDate, 1), OrderDateTime = OrderDateFROM AdventureWorks.Sales.SalesOrderHeaderORDER BY SalesOrderID desc/* ResultSalesOrderID OrderDate OrderDateTime75123 07/31/04 2004-07-31 00:00:00.000*/- Combining different style formats for date & time- Datetime formats- Datetime formats sqlDECLARE @Date DATETIMESET @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 stringSELECT 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 functionSELECT CURRENT_TIMESTAMP - 2012-01-05 07:02:10.577- SQL Server DATEADD functionSELECT DATEADD(month,2,-2012-12-09-) - 2013-02-09 00:00:00.000- SQL Server DATEDIFF functionSELECT DATEDIFF(day,-2012-12-09-,-2013-02-09-) - 62- SQL Server DATENAME functionSELECT DATENAME(month, -2012-12-09-) - DecemberSELECT DATENAME(weekday, -2012-12-09-) - Sunday- SQL Server DATEPART functionSELECT DATEPART(month, -2012-12-09-) - 12- SQL Server DAY functionSELECT DAY(-2012-12-09-) - 9- SQL Server GETDATE function- local NYC - EST - Eastern Standard Time zoneSELECT GETDATE() - 2012-01-05 07:02:10.577- SQL Server GETUTCDATE function- London - Greenwich Mean TimeSELECT GETUTCDATE() - 2012-01-05 12:02:10.577- SQL Server MONTH functionSELECT MONTH(-2012-12-09-) - 12- SQL Server YEAR functionSELECT 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.000SELECT 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.000SELECT 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.000SELECT 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.000SELECT 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.000SELECT DATEADD(mm,1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))- SQL last day of next month - 2012-02-28 00:00:00.000SELECT 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.000DECLARE @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.000DECLARE @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.000SELECT 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.000SELECT 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.000SELECT 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 2008USE AdventureWorks2008;GOCREATE 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 ENDGO- Test table-valued UDFSELECT * FROM fnAge(-1956-10-23-)SELECT * FROM dbo.fnAge(-1956-10-23-)/* ResultsYears Months Days52 4 1*/----- SQL date range between----- SQL between datesUSE AdventureWorks;- SQL betweenSELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeaderWHERE OrderDate BETWEEN -20040301- AND -20040315-- Result: 108- BETWEEN operator is equivalent to >=-AND-.<=SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeaderWHERE OrderDateBETWEEN -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 midnightare not included in the two queries above.*/- To include the entire day of 2004-03-15 use the following two solutionsSELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeaderWHERE OrderDate >= -20040301- AND OrderDate < -20040316-- SQL between with DATE type (SQL Server 2008)SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeaderWHERE CONVERT(DATE, OrderDate) BETWEEN -20040301- AND -20040315------ Non-standard format conversion: 2011 December 14- SQL datetime to stringSELECT [YYYY Month DD] =CAST(YEAR(GETDATE()) AS VARCHAR(4))+ - -+DATENAME(MM, GETDATE()) + - - +CAST(DAY(GETDATE()) AS VARCHAR(2))- Converting datetime to YYYYMMDDHHMMSS format: 20121214172638SELECT replace(convert(varchar, getdate(),111),-/-,-) +replace(convert(varchar, getdate(),108),-:-,-)- Datetime custom format conversion to YYYY_MM_DDselect CurrentDate=rtrim(year(getdate())) + -_- +right(-0- + rtrim(month(getdate())),2) + -_- +right(-0- + rtrim(day(getdate())),2)- Converting seconds to HH:MM:SS formatdeclare @Seconds intset @Seconds = 10000select 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 resultselect 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 datetimeSELECT 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 TYPESSMALLDATETIME date range:January 1, 1900 through June 6, 2079DATETIME date range:January 1, 1753 through December 31, 9999DATETIME2 date range (SQL Server 2008):January 1,1 AD through December 31, 9999 ADDATE 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 BYSELECT 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.PurchaseOrderHeaderORDER BY PurchaseOrderID DESC/* ResultsItaly USA Japan ISO25-07-2004 07-25-2004 2004/07/25 20040725*/- SQL Server convert date to integerDECLARE @Datetime datetimeSET @Datetime = -2012-10-23 10:21:05.345-SELECT DateAsInteger = CAST (CONVERT(varchar,@Datetime,112) as INT)- Result: 20121023- SQL Server convert integer to datetimeDECLARE @intDate intSET @intDate = 20120315SELECT 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 columnUSE tempdb;GOCREATE TABLE sqlConvertDateTime ( DatetimeCol datetime, DateCol char(8));INSERT sqlConvertDateTime (DatetimeCol) SELECT GETDATE()UPDATE sqlConvertDateTimeSET DateCol = CONVERT(char(10), DatetimeCol, 112)SELECT * FROM sqlConvertDateTime- SQL Server convert datetime- The string date column is converted into datetime columnUPDATE sqlConvertDateTimeSET DatetimeCol = CONVERT(Datetime, DateCol, 112)SELECT * FROM sqlConvertDateTime- Adding a day to the converted datetime column with DATEADDUPDATE sqlConvertDateTimeSET DatetimeCol = DATEADD(day, 1, CONVERT(Datetime, DateCol, 112))SELECT * FROM sqlConvertDateTime- Equivalent formulation- SQL Server cast datetimeUPDATE sqlConvertDateTimeSET DatetimeCol = DATEADD(dd, 1, CAST(DateCol AS datetime))SELECT * FROM sqlConvertDateTimeGODROP TABLE sqlConvertDateTimeGO/* First resultsDatetimeCol DateCol2014-12-25 16:04:15.373 20141225 *//* Second results:DatetimeCol DateCol2014-12-25 00:00:00.000 20141225 *//* Third results:DatetimeCol DateCol2014-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 methodDECLARE @Sequence table (Sequence int identity(1,1))DECLARE @i int; SET @i = 0DECLARE @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 + 1ENDSELECT MonthSequence = CAST(DATEADD(month, Sequence,@StartDate) AS varchar)FROM @SequenceGO/* Partial results:MonthSequenceJan 1 2012 12:00AMFeb 1 2012 12:00AMMar 1 2012 12:00AMApr 1 2012 12:00AM*/--------- SQL Server Server datetime internal storage- SQL Server datetime formats----- SQL Server datetime to hexSELECT Now=CURRENT_TIMESTAMP, HexNow=CAST(CURRENT_TIMESTAMP AS BINARY(8))/* ResultsNow HexNow2009-01-02 17:35:59.297 0x00009B850122092D*/- SQL Server date part - left 4 bytes - Days since 1900-01-01SELECT 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 MidnightSELECT 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 createSELECT TOP (5) FullName=convert(nvarchar(50),FirstName+- -+LastName), BirthDate = CONVERT(char(8), BirthDate,112), ModifiedDate = getdate()INTO EmployeeFROM AdventureWorks.HumanResources.Employee eINNER JOIN AdventureWorks.Person.Contact cON c.ContactID = e.ContactIDORDER BY EmployeeIDGO- SQL Server alter tableALTER TABLE Employee ALTER COLUMN FullName nvarchar(50) NOT NULLGOALTER TABLE EmployeeADD CONSTRAINT [PK_Employee] PRIMARY KEY (FullName )GO/* ResultsTable definition for the Employee tableNote: 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 FullNameGO/* ResultsFullName BirthDate ModifiedDateGuy Gilbert 19720515 2009-01-03 10:10:19.217Kevin Brown 19770603 2009-01-03 10:10:19.217Rob Walters 19650123 2009-01-03 10:10:19.217Roberto Tamburello 19641213 2009-01-03 10:10:19.217Thierry D-Hers 19490829 2009-01-03 10:10:19.217*/- SQL Server ageSELECT FullName, Age = DATEDIFF(YEAR, BirthDate, GETDATE()), RowMaintenanceDate = CAST (ModifiedDate AS varchar)FROM Employee ORDER BY FullNameGO/* ResultsFullName Age RowMaintenanceDateGuy Gilbert 37 Jan 3 2009 10:10AMKevin Brown 32 Jan 3 2009 10:10AMRob Walters 44 Jan 3 2009 10:10AMRoberto Tamburello 45 Jan 3 2009 10:10AMThierry 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 DATEADDSELECT 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 DATEDIFFSELECT 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 EmployeeWHERE FullName = -Rob Walters-ORDER BY FullNameGO/* ResultsImportant Note: age increments on Jan 1 (not as commonly calculated)FullName AgeDEC31 AgeJAN01 AgeJAN23 AgeJAN24 ModDateRob Walters 49 50 50 50 03-01-2009*/----- SQL combine integer date & time into datetime----- Datetime format sql- SQL stuffDECLARE @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 DateTimeValueFROM @DateTimeAsINT ORDER BY IDGO/* ResultsDateAsINT TimeAsINT DateTimeValue20121023 235959 2012-10-23 23:59:59.00020121023 10204 2012-10-23 01:02:04.00020121023 2350 2012-10-23 00:23:50.00020121023 244 2012-10-23 00:02:44.00020121023 50 2012-10-23 00:00:50.00020121023 6 2012-10-23 00:00:06.000*/----- SQL Server string to datetime, implicit conversion with assignmentUPDATE Employee SET ModifiedDate = -20150123-WHERE FullName = -Rob Walters-GOSELECT 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 functionUPDATE 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-GOSELECT BirthDate FROM Employee WHERE FullName = -Rob Walters-GO- Result: 19650123- Perform cleanup actionDROP TABLE Employee- SQL nocountSET NOCOUNT OFF;GO--------- sql isdate function----USE tempdb;- sql newid - random sortSELECT top(3) SalesOrderID,stringOrderDate = CAST (OrderDate AS varchar)INTO DateValidationFROM AdventureWorks.Sales.SalesOrderHeaderORDER BY NEWID()GOSELECT * FROM DateValidation/* ResultsSalesOrderID stringOrderDate56720 Oct 26 2003 12:00AM73737 Jun 25 2004 12:00AM70573 May 14 2004 12:00AM*/- SQL update with topUPDATE TOP(1) DateValidationSET stringOrderDate = -Apb 29 2004 12:00AM-GO- SQL string to datetime fails without validationSELECT SalesOrderID, OrderDate = CAST (stringOrderDate as datetime)FROM DateValidationGO/* Msg 242, Level 16, State 3, Line 1The conversion of a varchar data type to a datetime data type resulted in anout-of-range value.*/- sql isdate - filter for valid datesSELECT SalesOrderID, OrderDate = CAST (stringOrderDate as datetime)FROM DateValidationWHERE ISDATE(stringOrderDate) = 1GO/* ResultsSalesOrderID OrderDate73737 2004-06-25 00:00:00.00070573 2004-05-14 00:00:00.000*/- SQL drop tableDROP TABLE DateValidationGo----- SELECT between two specified dates - assumption TIME part is 00:00:00.000----- SQL datetime between- SQL select between two datesSELECT EmployeeID, RateChangeDateFROM AdventureWorks.HumanResources.EmployeePayHistoryWHERE RateChangeDate >= -1997-11-01- AND RateChangeDate < DATEADD(dd,1,-1998-01-05-)GO/* ResultsEmployeeID RateChangeDate3 1997-12-12 00:00:00.0004 1998-01-05 00:00:00.000*//* Equivalent to- SQL datetime rangeSELECT EmployeeID, RateChangeDateFROM AdventureWorks.HumanResources.EmployeePayHistoryWHERE 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 settingsSET 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 - UDFCREATE FUNCTION fnMondayDate (@Year INT, @Month INT, @MondayOrdinal INT)RETURNS DATETIMEAS 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) ENDGO- Test Datetime UDF- Third Monday in Feb, 2015SELECT dbo.fnMondayDate(2016,2,3)- 2015-02-16 00:00:00.000- First Monday of current monthSELECT dbo.fnMondayDate(Year(getdate()),Month(getdate()),1)- 2009-02-02 00:00:00.000----