November 11, 2013 at 3:51 am
select dateformat from sys.syslanguages where name = @@LANGUAGE
--mdy
select getdate()
--'2013-11-11 16:00:04.960'
Dear All,
Above are the stats at the time of creating this post.
Below are the some select statements and their outputs where I'm trying to convert a date value into Varchar and then again converting output into date to get only the date part (time part will become '00:00:00' or trimmed)
Can somebody please explain, why we have different behaviors for similar type of select statements !!!
select CONVERT(DATETIME, CONVERT(VARCHAR(12), getdate()))
--'2013-11-11'
select CONVERT(DATETIME, CONVERT(VARCHAR(10), '2013-11-11 16:00:04.960'))
--'2013-11-11'
select CONVERT(DATETIME, CONVERT(VARCHAR(10), getdate()))
--'Conversion failed when converting date and/or time from character string.'
select CONVERT(DATETIME, CONVERT(VARCHAR(12), '2013-11-11 16:00:04.960'))
--'Conversion failed when converting date and/or time from character string.'
select CONVERT(VARCHAR(12), getdate())
--'Nov 11 2013 '
select CONVERT(VARCHAR(10), '2013-11-11 16:00:04.960')
--'2013-11-11'
select CONVERT(VARCHAR(10), getdate())
--'Nov 11 201'[/b]
select CONVERT(VARCHAR(12), '2013-11-11 16:00:04.960')
--'2013-11-11 1'
November 11, 2013 at 4:04 am
select CONVERT(DATETIME, CONVERT(VARCHAR(10), getdate()))
--'Conversion failed when converting date and/or time from character string.'
In this one you're trying to convert from a datetime to a varchar and back to a datetime, however there's not enough space to convert properly. You specified Varchar(10), converting to a 10-character string with the default format results in "Nov 11 201". That obviously isn't a valid date and hence the conversion back to datetime fails.
select CONVERT(DATETIME, CONVERT(VARCHAR(12), '2013-11-11 16:00:04.960'))
--'Conversion failed when converting date and/or time from character string.'
In this case, you're starting with a string, converting that to a string (which just truncates it as there's nothing else to be done), then attempts to convert that to datetime, which fails because the truncation left the string unable to convert to a date.
This one is completely equivalent to
select CONVERT(DATETIME, LEFT('2013-11-11 16:00:04.960',12)), since the input was a string, not a date.
The left-most 12 characters of the string specified are "2013-11-11 1" That also obviously isn't a valid date and hence the conversion to datetime fails.
String conversions are about the least efficient way of trimming a time from a date. Since you're using 2008 or later, how about just converting to DATE?
SELECT CAST('2013-11-11 16:00:04.960' AS DATE)
SELECT CAST(GETDATE() AS DATE)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 11, 2013 at 5:23 am
What about
select CONVERT(VARCHAR(12), getdate())
output of select getdate() is 2013-11-11 17:50:53.347
but output of select CONVERT(VARCHAR(12), getdate()) is Nov 11 2013 (+ 1 blank space)
November 11, 2013 at 5:27 am
Convert allows an explicit format to be specified. If you don't specify one, you get the default format, which is "mon dd yyyy hh:miAM" (as per BoL). If you want a different format, specify the appropriate format code.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply