|
Execute the following
script in Query Editor to demonstrate conversion from string variable to datetime and from string literal to datetime.
-- 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)
-- CONVERT FROM STRING TO DATETIME FORMAT
SELECT [Datetime] = CONVERT(datetime,'Feb 09 2012 4:36PM')
SELECT [Datetime] = CONVERT(datetime,'2/09/2012')
SELECT [Datetime] = CONVERT(datetime,'12.02.09')
SELECT [Datetime] = CONVERT(datetime,'09/02/12')
SELECT [Datetime] = CONVERT(datetime,'09.02.12')
SELECT [Datetime] = CONVERT(datetime,'09-02-12')
SELECT [Datetime] = CONVERT(datetime,'09-Feb-12')
SELECT [Datetime] = CONVERT(datetime,'16:26:08')
SELECT [Datetime] = CONVERT(datetime,'Feb 09 2012 4:46:08:620PM')
SELECT [Datetime] = CONVERT(datetime,'2/09/2012')
SELECT [Datetime] = CONVERT(datetime,'6/2/2009')
SELECT [Datetime] = CONVERT(datetime,'09 Feb 2012 16:16:08:023')
SELECT [Datetime] = CONVERT(datetime,'16:26:08:037')
SELECT [Datetime] = CONVERT(datetime,'2/09/2012 16:26')
SELECT [Datetime] = CONVERT(datetime,'2/09/2012')
SELECT [Datetime] = CONVERT(datetime,'16:26:08')
SELECT [Datetime] = CONVERT(datetime,'Feb 09 2012 4:33PM')
SELECT [Datetime] = CONVERT(datetime,'2/09/2012')
SELECT [Datetime] = CONVERT(datetime,'2012.02.09')
SELECT [Datetime] = CONVERT(datetime,'09/02/2012')
SELECT [Datetime] = CONVERT(datetime,'09.02.2012')
SELECT [Datetime] = CONVERT(datetime,'09-02-2012')
SELECT [Datetime] = CONVERT(datetime,'09-Feb-12')
SELECT [Datetime] = CONVERT(datetime,'16:26:08')
SELECT [Datetime] = CONVERT(datetime,'Feb 09 2012 4:44:08:627PM')
SELECT [Datetime] = CONVERT(datetime,'2/09/2012')
SELECT [Datetime] = CONVERT(datetime,'20120131')
SELECT [Datetime] = CONVERT(datetime,'09 Feb 2012 16:28:08:457')
SELECT [Datetime] = CONVERT(datetime,'16:26:08:127')
SELECT [Datetime] = CONVERT(datetime,'2/09/2012 16:04')
SELECT [Datetime] = CONVERT(datetime,'2012-02-09T16:55:08.085')
SELECT [Datetime] = CONVERT(datetime,'01/01/1900 6:11:08:780PM')
|
