|
Execute the following
Microsoft SQL Server 2008 T-SQL script in Query Editor to demonstrate conversion from string variable to datetime and from string literal to datetime.
------------
-- CONVERT FROM STRING TO DATETIME FORMAT
------------
SELECT DT = CONVERT(datetime,'Oct 23 2016 4:36PM') -- 2016-10-23 16:36:00.000
SELECT DT = CONVERT(datetime,'10/23/2016') -- 2016-10-23 00:00:00.000
SELECT DT = CONVERT(datetime,'12.02.09') -- 2009-12-02 00:00:00.000
SELECT DT = CONVERT(datetime,'09/02/12') -- 2012-09-02 00:00:00.000
SELECT DT = CONVERT(datetime,'09.02.12') -- 2012-09-02 00:00:00.000
SELECT DT = CONVERT(datetime,'09-02-12') -- 2012-09-02 00:00:00.000
SELECT DT = CONVERT(datetime,'09-Oct-12') -- 2012-10-09 00:00:00.000
SELECT DT = CONVERT(datetime,'16:26:08') -- 1900-01-01 16:26:08.000
SELECT DT = CONVERT(datetime,'Oct 23 2016 4:46:08:620PM') -- 2016-10-23 16:46:08.620
SELECT DT = CONVERT(datetime,'6/2/2009') -- 2009-06-02 00:00:00.000
SELECT DT = CONVERT(datetime,'23 Oct 2016 16:16:08:023') -- 2016-10-23 16:16:08.023
SELECT DT = CONVERT(datetime,'16:26:08:037') -- 1900-01-01 16:26:08.037
SELECT DT = CONVERT(datetime,'10/23/2016 16:26') -- 2016-10-23 16:26:00.000
SELECT DT = CONVERT(datetime,'16:26:08') -- 1900-01-01 16:26:08.000
SELECT DT = CONVERT(datetime,'Oct 23 2016 4:33PM') -- 2016-10-23 16:33:00.000
SELECT DT = CONVERT(datetime,'2016.10.23') -- 2016-10-23 00:00:00.000
SELECT DT = CONVERT(datetime,'23-Oct-16') -- 2016-10-23 00:00:00.000
SELECT DT = CONVERT(datetime,'16:26:08') -- 1900-01-01 16:26:08.000
SELECT DT = CONVERT(datetime,'20160131') -- 2016-01-31 00:00:00.000
SELECT DT = CONVERT(datetime,'23 Oct 2016 16:28:08:457') -- 2016-10-23 16:28:08.457
SELECT DT = CONVERT(datetime,'16:26:08:127') -- 1900-01-01 16:26:08.127
SELECT DT = CONVERT(datetime,'10/23/2016 16:04') -- 2016-10-23 16:04:00.000
SELECT DT = CONVERT(datetime,'2016-10-23T16:55:08.085') -- 2016-10-23 16:55:08.087
SELECT DT = CONVERT(datetime,'01/01/1900 6:11:08:780PM') -- 1900-01-01 18:11:08.780
More date & time conversion at the following link:
http://sqlusa.com/bestpractices/datetimeconversion/
------------
-- CONVERT FROM STRING VARIABLE TO DATETIME FORMAT
------------
DECLARE
@DateTimeValue varchar(35),
@DateValue char(8),
@TimeValue char(6),
@String1 varchar(35),
@String2 varchar(35)
SELECT
@DateValue = '20100718',
@TimeValue = '211920',
@String1 = '20110322 12:34:55.854',
@String2 = 'Feb 22 2012 4:26:08:020PM'
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,
StringInput1 = @String1,
StringInput2 = @String2,
DateTimeOutput = @DateTimeValue,
DateTimeConversion1 = convert(datetime, @String1),
DateTimeConversion2 = convert(datetime, @String2)
/**********************************************
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:
January 1,1 AD through December 31, 9999 AD
DATE date range:
January 1, 1 AD through December 31, 9999 AD
-- The following statement will give an error
SELECT CONVERT(smalldatetime, '2100-01-01')
/*
Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a smalldatetime data type
resulted in an out-of-range value.
*/
*********************************************/
-- Selected named date styles
DECLARE @DateTimeValue varchar(32)
SET DATEFORMAT mdy
-- US-Style
SELECT @DateTimeValue = '10/23/2016'
SELECT StringDate=@DateTimeValue,
[US-Style] = CONVERT(datetime, @DatetimeValue)
SELECT @DateTimeValue = '10/23/2016 23:01:05'
SELECT StringDate = @DateTimeValue,
[US-Style] = CONVERT(datetime, @DatetimeValue)
-- ANSI-Style
SELECT @DateTimeValue = '16.10.23 15:01:05'
SELECT StringDate = @DateTimeValue,
[ANSI-Style] = CONVERT(datetime, @DatetimeValue, 2)
SELECT @DateTimeValue = '2016.10.23 04:01 PM'
SELECT StringDate = @DateTimeValue,
[ANSI-Style] = CONVERT(datetime, @DatetimeValue, 102)
-- UK-Style, British/French
SELECT @DateTimeValue = '23/10/16 23:01:05'
SELECT StringDate = @DateTimeValue,
[UK-Style] = CONVERT(datetime, @DatetimeValue, 3)
SELECT @DateTimeValue = '23/10/2016 04:01 PM'
SELECT StringDate = @DateTimeValue,
[UK-Style] = CONVERT(datetime, @DatetimeValue, 103)
-- German-Style
SELECT @DateTimeValue = '23.10.16 23:01:05'
SELECT StringDate = @DateTimeValue,
[German-Style] = CONVERT(datetime, @DatetimeValue, 4)
SELECT @DateTimeValue = '23.10.2016 04:01 PM'
SELECT StringDate = @DateTimeValue,
[German-Style] = CONVERT(datetime, @DatetimeValue, 104)
-- Double conversion to US-Style 107 with century: Oct 23, 2016
SET @DateTimeValue='10/23/16'
SELECT StringDate=@DateTimeValue,
[US-Style] = CONVERT(varchar, CONVERT(datetime, @DateTimeValue),107)
-- Using DATEFORMAT - UK-Style
SET @DateTimeValue='23/10/16'
SET DATEFORMAT dmy
SELECT StringDate=@DateTimeValue,
[Date Time] = CONVERT(datetime, @DatetimeValue)
GO
------------
-- String date validation function ISDATE - returns 1 or 0
------------
DECLARE @StringDate varchar(32) = '2012-03-15 18:50'
IF EXISTS( SELECT 1 WHERE ISDATE(@StringDate) = 1)
PRINT 'VALID DATE:' + @StringDate
ELSE
PRINT 'INVALID DATE:' + @StringDate
------------
DECLARE @StringDate varchar(32) = '20122-03-15 18:50'
IF EXISTS( SELECT 1 WHERE ISDATE(@StringDate) = 1)
PRINT 'VALID DATE:' + @StringDate
ELSE
PRINT 'INVALID DATE:' + @StringDate
------------
------------
-- CONVERT ISO 8601 datetime with zone offset
------------
DECLARE @ISO8601DT VARCHAR(48) = '2018-03-15T22:10:20.1234567+05:00'
SELECT CAST( @ISO8601DT AS datetime2) AS 'datetime2'
,CAST( @ISO8601DT AS datetimeoffset) AS 'datetimeoffset'
/* datetime2 datetimeoffset
2018-03-15 22:10:20.1234567 2018-03-15 22:10:20.1234567 +05:00 */
|
