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