How to convert from string to datetime MSSQL? Source : How to convert from string to datetime? Post date : 21-08-2013 How to convert from string to datetime MSSQL? -- SQL Server string to date / datetime conversion - datetime string format sql server -- MSSQL string to datetime conversion - convert char to date - convert varchar to date -- Subtract 100 from style number (format) for yy instead yyyy (or ccyy with century) SELECT convert(datetime, 'Oct 23 2012 11:01AM', 100) -- mon dd yyyy hh:mmAM (or PM) SELECT convert(datetime, 'Oct 23 2012 11:01AM') -- 2012-10-23 11:01:00.000
-- Without century (yy) string date conversion - convert string to datetime function SELECT convert(datetime, 'Oct 23 12 11:01AM', 0) -- mon dd yy hh:mmAM (or PM) SELECT convert(datetime, 'Oct 23 12 11:01AM') -- 2012-10-23 11:01:00.000
-- Convert string to datetime sql - convert string to date sql - sql dates format -- T-SQL convert string to datetime - SQL Server convert string to date SELECT convert(datetime, '10/23/2016', 101) -- mm/dd/yyyy SELECT convert(datetime, '2016.10.23', 102) -- yyyy.mm.dd ANSI date with century SELECT convert(datetime, '23/10/2016', 103) -- dd/mm/yyyy SELECT convert(datetime, '23.10.2016', 104) -- dd.mm.yyyy SELECT convert(datetime, '23-10-2016', 105) -- dd-mm-yyyy -- mon types are nondeterministic conversions, dependent on language setting SELECT convert(datetime, '23 OCT 2016', 106) -- dd mon yyyy SELECT convert(datetime, 'Oct 23, 2016', 107) -- mon dd, yyyy -- 2016-10-23 00:00:00.000 SELECT convert(datetime, '20:10:44', 108) -- hh:mm:ss -- 1900-01-01 20:10:44.000
-- mon dd yyyy hh:mm:ss:mmmAM (or PM) - sql time format - SQL Server datetime format SELECT convert(datetime, 'Oct 23 2016 11:02:44:013AM', 109) -- 2016-10-23 11:02:44.013 SELECT convert(datetime, '10-23-2016', 110) -- mm-dd-yyyy SELECT convert(datetime, '2016/10/23', 111) -- yyyy/mm/dd -- YYYYMMDD ISO date format works at any language setting - international standard SELECT convert(datetime, '20161023') SELECT convert(datetime, '20161023', 112) -- ISO yyyymmdd -- 2016-10-23 00:00:00.000 SELECT convert(datetime, '23 Oct 2016 11:02:07:577', 113) -- dd mon yyyy hh:mm:ss:mmm -- 2016-10-23 11:02:07.577 SELECT convert(datetime, '20:10:25:300', 114) -- hh:mm:ss:mmm(24h) -- 1900-01-01 20:10:25.300 SELECT convert(datetime, '2016-10-23 20:44:11', 120) -- yyyy-mm-dd hh:mm:ss(24h) -- 2016-10-23 20:44:11.000 SELECT convert(datetime, '2016-10-23 20:44:11.500', 121) -- yyyy-mm-dd hh:mm:ss.mmm -- 2016-10-23 20:44:11.500
-- Style 126 is ISO 8601 format: international standard - works with any language setting SELECT convert(datetime, '2008-10-23T18:52:47.513', 126) -- yyyy-mm-ddThh:mm:ss(.mmm) -- 2008-10-23 18:52:47.513 SELECT convert(datetime, N'23 شوال 1429 6:52:47:513PM', 130) -- Islamic/Hijri date SELECT convert(datetime, '23/10/1429 6:52:47:513PM', 131) -- Islamic/Hijri date
-- Convert DDMMYYYY format to datetime - sql server to date / datetime SELECT convert(datetime, STUFF(STUFF('31012016',3,0,'-'),6,0,'-'), 105) -- 2016-01-31 00:00:00.000 -- SQL Server T-SQL string to datetime conversion without century - some exceptions -- nondeterministic means language setting dependent such as Mar/Mär/mars/márc SELECT convert(datetime, 'Oct 23 16 11:02:44AM') -- Default SELECT convert(datetime, '10/23/16', 1) -- mm/dd/yy U.S. SELECT convert(datetime, '16.10.23', 2) -- yy.mm.dd ANSI SELECT convert(datetime, '23/10/16', 3) -- dd/mm/yy UK/FR SELECT convert(datetime, '23.10.16', 4) -- dd.mm.yy German SELECT convert(datetime, '23-10-16', 5) -- dd-mm-yy Italian SELECT convert(datetime, '23 OCT 16', 6) -- dd mon yy non-det. SELECT convert(datetime, 'Oct 23, 16', 7) -- mon dd, yy non-det. SELECT convert(datetime, '20:10:44', 8) -- hh:mm:ss SELECT convert(datetime, 'Oct 23 16 11:02:44:013AM', 9) -- Default with msec SELECT convert(datetime, '10-23-16', 10) -- mm-dd-yy U.S. SELECT convert(datetime, '16/10/23', 11) -- yy/mm/dd Japan SELECT convert(datetime, '161023', 12) -- yymmdd ISO SELECT convert(datetime, '23 Oct 16 11:02:07:577', 13) -- dd mon yy hh:mm:ss:mmm EU dflt SELECT convert(datetime, '20:10:25:300', 14) -- hh:mm:ss:mmm(24h) SELECT convert(datetime, '2016-10-23 20:44:11',20) -- yyyy-mm-dd hh:mm:ss(24h) ODBC can. SELECT convert(datetime, '2016-10-23 20:44:11.500', 21)-- yyyy-mm-dd hh:mm:ss.mmm ODBC ------------ -- SQL Datetime Data Type: Combine date & time string into datetime - sql hh mm ss -- String to datetime - mssql datetime - sql convert date - sql concatenate string DECLARE @DateTimeValue varchar(32), @DateValue char(8), @TimeValue char(6)
SELECT @DateValue = '20120718', @TimeValue = '211920' SELECT @DateTimeValue = convert(varchar, convert(datetime, @DateValue), 111) + ' ' + substring(@TimeValue, 1, 2) + ':' + substring(@TimeValue, 3, 2) + ':' + substring(@TimeValue, 5, 2) SELECT DateInput = @DateValue, TimeInput = @TimeValue, DateTimeOutput = @DateTimeValue; /* DateInput TimeInput DateTimeOutput 20120718 211920 2012/07/18 21:19:20 */ SELECT CONVERT(binary(8), getdate()) -- 0x00009E4D 00C01272 SELECT CONVERT(binary(4), convert(smalldatetime,getdate())) -- 0x9E4D 02BC -- This is how a datetime looks in 8 bytes DECLARE @dtHex binary(8)= 0x00009966002d3344; DECLARE @dt datetime = @dtHex SELECT @dt -- 2007-07-09 02:44:34.147 ------------ */ ------------ -- SQL Server 2012 New Date & Time Related Functions ------------ SELECT DATEFROMPARTS ( 2016, 10, 23 ) AS RealDate; -- 2016-10-23
SELECT DATETIMEFROMPARTS ( 2016, 10, 23, 10, 10, 10, 500 ) AS RealDateTime; -- 2016-10-23 10:10:10.500
SELECT EOMONTH('20140201'); -- 2014-02-28 SELECT EOMONTH('20160201'); -- 2016-02-29 SELECT EOMONTH('20160201',1); -- 2016-03-31
SELECT FORMAT ( getdate(), 'yyyy/MM/dd hh:mm:ss tt', 'en-US' ); -- 2016/07/30 03:39:48 AM SELECT FORMAT ( getdate(), 'd', 'en-US' ); -- 7/30/2016
SELECT PARSE('SAT, 13 December 2014' AS datetime USING 'en-US') AS [Date&Time]; -- 2014-12-13 00:00:00.000
SELECT TRY_PARSE('SAT, 13 December 2014' AS datetime USING 'en-US') AS [Date&Time]; -- 2014-12-13 00:00:00.000
SELECT TRY_CONVERT(datetime, '13 December 2014' ) AS [Date&Time]; -- 2014-12-13 00:00:00.000 ------------
-- SQL convert seconds to HH:MM:SS - sql times format - sql hh mm DECLARE @Seconds INT SET @Seconds = 20000 SELECT HH = @Seconds / 3600, MM = (@Seconds%3600) / 60, SS = (@Seconds%60) /* HH MM SS 5 33 20 */ ------------ -- SQL Server Date Only from DATETIME column - get date only -- T-SQL just date - truncate time from datetime - remove time part ------------ DECLARE @Now datetime = CURRENT_TIMESTAMP -- getdate() SELECT DateAndTime = @Now -- Date portion and Time portion ,DateString = REPLACE(LEFT(CONVERT (varchar, @Now, 112),10),' ','-') ,[Date] = CONVERT(DATE, @Now) -- SQL Server 2008 and on - date part ,Midnight1 = dateadd(day, datediff(day,0, @Now), 0) ,Midnight2 = CONVERT(DATETIME,CONVERT(int, @Now)) ,Midnight3 = CONVERT(DATETIME,CONVERT(BIGINT,@Now) & (POWER(Convert(bigint,2),32)-1)) /* DateAndTime DateString Date Midnight1 Midnight2 Midnight3 2010-11-02 08:00:33.657 20101102 2010-11-02 2010-11-02 00:00:00.000 2010-11-02 00:00:00.000 2010-11-02 00:00:00.000 */ ------------ -- SQL Server 2008 convert datetime to date - sql yyyy mm dd SELECT TOP (3) OrderDate = CONVERT(date, OrderDate), Today = CONVERT(date, getdate()) FROM AdventureWorks2008.Sales.SalesOrderHeader ORDER BY newid(); /* OrderDate Today 2004-02-15 2012-06-18 .....*/ ------------ -- SQL date yyyy mm dd - sqlserver yyyy mm dd - date format yyyymmdd SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD] /* YYYY/MM/DD 2015/07/11 */ SELECT CONVERT(VARCHAR(10), GETDATE(), 112) AS [YYYYMMDD] /* YYYYMMDD 20150711 */ SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111),'/',' ') AS [YYYY MM DD] /* YYYY MM DD 2015 07 11 */ -- SQL convert date string to datetime - time set to 00:00:00.000 or 12:00AM PRINT CONVERT(datetime,'07-10-2012',110) -- Jul 10 2012 12:00AM PRINT CONVERT(datetime,'2012/07/10',111) -- Jul 10 2012 12:00AM PRINT CONVERT(datetime,'20120710', 112) -- Jul 10 2012 12:00AM ------------ -- UNIX to SQL Server datetime conversion declare @UNIX bigint = 1477216861; select dateadd(ss,@UNIX,'19700101'); -- 2016-10-23 10:01:01.000 -- String to date conversion - sql date yyyy mm dd - sql date formatting -- SQL Server cast string to date - sql convert date to datetime SELECT [Date] = CAST (@DateValue AS datetime) -- 2012-07-18 00:00:00.000
-- SQL convert string date to different style - sql date string formatting SELECT CONVERT(varchar, CONVERT(datetime, '20140508'), 100) -- May 8 2014 12:00AM -- SQL Server convert date to integer DECLARE @Date datetime; SET @Date = getdate(); SELECT DateAsInteger = CAST (CONVERT(varchar,@Date,112) as INT); -- Result: 20161225
-- SQL Server convert integer to datetime DECLARE @iDate int SET @iDate = 20151225 SELECT IntegerToDatetime = CAST(convert(varchar,@iDate) as datetime) -- 2015-12-25 00:00:00.000
-- Alternates: date-only datetime values -- SQL Server floor date - sql convert datetime SELECT [DATE-ONLY]=CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE()))) SELECT [DATE-ONLY]=CONVERT(DATETIME, FLOOR(CONVERT(MONEY, GETDATE()))) -- SQL Server cast string to datetime -- SQL Server datetime to string convert SELECT [DATE-ONLY]=CAST(CONVERT(varchar, GETDATE(), 101) AS DATETIME) -- SQL Server dateadd function - T-SQL datediff function -- SQL strip time from date - MSSQL strip time from datetime SELECT getdate() ,dateadd(dd, datediff(dd, 0, getdate()), 0) -- Results: 2016-01-23 05:35:52.793 2016-01-23 00:00:00.000 -- String date - 10 bytes of storage SELECT [STRING DATE]=CONVERT(varchar, GETDATE(), 110) SELECT [STRING DATE]=CONVERT(varchar, CURRENT_TIMESTAMP, 110) -- Same results: 01-02-2012
-- SQL Server cast datetime as string - sql datetime formatting SELECT stringDateTime=CAST (getdate() as varchar) -- Dec 29 2012 3:47AM ---------- -- SQL date range BETWEEN operator ---------- -- SQL date range select - date range search - T-SQL date range query -- Count Sales Orders for 2003 OCT-NOV DECLARE @StartDate DATETIME, @EndDate DATETIME SET @StartDate = convert(DATETIME,'10/01/2003',101) SET @EndDate = convert(DATETIME,'11/30/2003',101)
SELECT @StartDate, @EndDate -- 2003-10-01 00:00:00.000 2003-11-30 00:00:00.000 SELECT dateadd(DAY,1,@EndDate), dateadd(ms,-3,dateadd(DAY,1,@EndDate)) -- 2003-12-01 00:00:00.000 2003-11-30 23:59:59.997
-- MSSQL date range select using >= and < SELECT [Sales Orders for 2003 OCT-NOV] = COUNT(* ) FROM Sales.SalesOrderHeader WHERE OrderDate >= @StartDate AND OrderDate < dateadd(DAY,1,@EndDate) /* Sales Orders for 2003 OCT-NOV 3668 */
-- Equivalent date range query using BETWEEN comparison -- It requires a bit of trick programming SELECT [Sales Orders for 2003 OCT-NOV] = COUNT(* ) FROM Sales.SalesOrderHeader WHERE OrderDate BETWEEN @StartDate AND dateadd(ms,-3,dateadd(DAY,1,@EndDate)) -- 3668
USE AdventureWorks; -- SQL between string dates SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader WHERE OrderDate BETWEEN '20040201' AND '20040210' -- Result: 108
-- SQL BETWEEN dates without time - time stripped - time removed - date part only SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader WHERE datediff(dd,0,OrderDate) BETWEEN datediff(dd,0,'20040201 12:11:39') AND datediff(dd,0,'20040210 14:33:19') -- 108 -- BETWEEN is equivalent to >=...AND....<= SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader WHERE OrderDate BETWEEN '2004-02-01 00:00:00.000' AND '2004-02-10 00:00:00.000' /* Orders with OrderDates '2004-02-10 00:00:01.000' - 1 second after midnight (12:00AM) '2004-02-10 00:01:00.000' - 1 minute after midnight '2004-02-10 01:00:00.000' - 1 hour after midnight are not included in the two queries above. */ -- To include the entire day of 2004-02-10 use: SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader WHERE OrderDate >= '20040201' AND OrderDate < '20040211' ---------- -- Calculate week ranges in a year ---------- DECLARE @Year INT = '2016'; WITH cteDays AS (SELECT DayOfYear=Dateadd(dd, number, CONVERT(DATE, CONVERT(char(4),@Year)+'0101')) FROM master.dbo.spt_values WHERE type='P'), CTE AS (SELECT DayOfYear, WeekOfYear=DATEPART(week,DayOfYear) FROM cteDays WHERE YEAR(DayOfYear)= @YEAR) SELECT WeekOfYear, StartOfWeek=MIN(DayOfYear), EndOfWeek=MAX(DayOfYear) FROM CTE GROUP BY WeekOfYear ORDER BY WeekOfYear ------------ -- Date validation function ISDATE - returns 1 or 0 - SQL datetime functions ------------ DECLARE @StringDate varchar(32) SET @StringDate = '2011-03-15 18:50' IF EXISTS( SELECT * WHERE ISDATE(@StringDate) = 1) PRINT 'VALID DATE: ' + @StringDate ELSE PRINT 'INVALID DATE: ' + @StringDate GO -- Result: VALID DATE: 2011-03-15 18:50
DECLARE @StringDate varchar(32) SET @StringDate = '20112-03-15 18:50' IF EXISTS( SELECT * WHERE ISDATE(@StringDate) = 1) PRINT 'VALID DATE: ' + @StringDate ELSE PRINT 'INVALID DATE: ' + @StringDate -- Result: INVALID DATE: 20112-03-15 18:50 -- First and last day of date periods - SQL Server 2008 and on code DECLARE @Date DATE = '20161023' SELECT ReferenceDate = @Date SELECT FirstDayOfYear = CONVERT(DATE, dateadd(yy, datediff(yy,0, @Date),0)) SELECT LastDayOfYear = CONVERT(DATE, dateadd(yy, datediff(yy,0, @Date)+1,-1)) SELECT FDofSemester = CONVERT(DATE, dateadd(qq,((datediff(qq,0,@Date)/2)*2),0)) SELECT LastDayOfSemester = CONVERT(DATE, dateadd(qq,((datediff(qq,0,@Date)/2)*2)+2,-1)) SELECT FirstDayOfQuarter = CONVERT(DATE, dateadd(qq, datediff(qq,0, @Date),0)) -- 2016-10-01 SELECT LastDayOfQuarter = CONVERT(DATE, dateadd(qq, datediff(qq,0,@Date)+1,-1)) -- 2016-12-31 SELECT FirstDayOfMonth = CONVERT(DATE, dateadd(mm, datediff(mm,0, @Date),0)) SELECT LastDayOfMonth = CONVERT(DATE, dateadd(mm, datediff(mm,0, @Date)+1,-1)) SELECT FirstDayOfWeek = CONVERT(DATE, dateadd(wk, datediff(wk,0, @Date),0)) SELECT LastDayOfWeek = CONVERT(DATE, dateadd(wk, datediff(wk,0, @Date)+1,-1)) -- 2016-10-30
-- Month sequence generator - sequential numbers / dates DECLARE @Date date = '2000-01-01' SELECT MonthStart=dateadd(MM, number, @Date) FROM master.dbo.spt_values WHERE type='P' AND dateadd(MM, number, @Date) <= CURRENT_TIMESTAMP ORDER BY MonthStart /* MonthStart 2000-01-01 2000-02-01 2000-03-01 ....*/
|
NEWS