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?

Execute the following SQL Server T-SQL scripts in Management Studio Query Editor to convert from string format to datetime and demonstrate the use of the ISDATE function in date and time conversions:

 

-- SQL declare local variables

DECLARE

@DateTimeValue varchar(30),

@DateValue char(8),

@TimeValue char(6)

 

-- SQL assign values

SELECT

@DateValue = '20081023',

@TimeValue = '211920'

 

-- SQL string to datetime assembly  

SELECT @DateTimeValue =

convert(varchar, convert(datetime, @DateValue), 110)

+ ' ' + substring(@TimeValue, 1, 2)

+ ':' + substring(@TimeValue, 3, 2)

+ ':' + substring(@TimeValue, 5, 2)

 

 

SELECT

DateInput = @DateValue,

TimeInput = @TimeValue,

DateTimeOutput = @DateTimeValue,

DateTimeFormat = convert(datetime, @DateTimeValue)

/* Results

 

DateInput   TimeInput   DateTimeOutput          DateTimeFormat

20081023    211920      10-23-2008 21:19:20     2008-10-23 21:19:20.000

*/

------------

------------

-- sql isdate function

------------

USE tempdb;

-- sql newid - random sort

-- sql select into table create

SELECT top(3) PurchaseOrderID,

stringOrderDate = CAST (OrderDate AS varchar)

INTO DateValidation

FROM AdventureWorks.Purchasing.PurchaseOrderHeader

ORDER BY NEWID()

GO

SELECT * FROM DateValidation

/* Results

 

PurchaseOrderID   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 PurchaseOrderID, 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 PurchaseOrderID, OrderDate = CAST (stringOrderDate as datetime)

FROM DateValidation

WHERE ISDATE(stringOrderDate) = 1

GO

/* Results

 

PurchaseOrderID   OrderDate

73737             2004-06-25 00:00:00.000

70573             2004-05-14 00:00:00.000

*/

-- SQL drop table

DROP TABLE DateValidation

Go

------------

-- Related link:

   http://sqlusa.com/bestpractices/datetimeconversion/

 

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