SQLUSA.com
SQL SERVER 2008 GRAND SLAM
FREE TRIAL  CLICK HERE TO ORDER  SEARCH
SQL JOBS SQL Server Training Scripts JOB BANK
  SQL Server 2008 Training Scripts  
  SQL Server 2005 Training Scripts  
News SQL Server Articles SQL Format

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/

 

Order SQL 2008 GRAND SLAM Today!
SQLUSA.com Home Page
SQL Server Training at www.sqlusa.com.
SQL Server 2008 Video Training at www.sqlusa.com.
SQL Server 2005 Training Videos at www.sqlusa.com.
Microsoft SQL Server 2000 Training Videos at www.sqlusa.com.

FREE SQL & Business Intelligence / OLAP Short Videos on YOUTUBE.com

Microsoft Community Contributor 2011
Invest in Your SUCCESS!

Search SQLUSA FREE SQL Server Articles & FREE T-SQL Scripts


Copyright 2005-2011, SMI Corp. All Rights Reserved.

SQL Server 2012 is a program product of Microsoft Corporation.
SQL Server 2008 is a program product of Microsoft Corporation.
SQL Server 2005 is a program product of Microsoft Corporation.
SQL Server 2000 is a program product of Microsoft Corporation.