DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to convert from string to datetime in T-SQL?

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    */

 

 

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE