August 29, 2013 at 5:55 am
Hi if any one have solution for this please let me know.
i am getting the below convertion issue when i run the below mentioned query.
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting datetime from character string.
Declare @StartDate Datetime
set @StartDate =convert(varchar(10), getdate(), 101)+(CAST(DATEPART(hh,(SELECT [Value] FROM tbltime WHERE NAME='TIME')) AS VARCHAR) +':'+ CAST(DATEPART(mi,(SELECT [Value] FROM tbltime WHERE NAME='TIME')) as VARCHAR) +':'+ CAST(DATEPART(ss,(SELECT [Value] FROM tbltime WHERE NAME='TIME')) AS VARCHAR)+CAST(DATEPART(ss,(SELECT [Value] FROM tbltime WHERE NAME='TIME')) AS VARCHAR))
print @StartDate
please let me know if anybody have the solution for the same.
i need to get the result as mentioned below.
08/29/201316:30:00
August 29, 2013 at 6:07 am
You didn't seperate the date and time part with a space. And in the last part of your query you are using the "DATEPART(ss,...)" twice and you are also not seperating these two with a ".". This will result in a notation like "2013-08-29 14:04:5959" and that string cannot be converted to the datetime format of the variable.
August 29, 2013 at 6:11 am
Here is the corrected statement:
SET @StartDate = convert(VARCHAR(10), getdate(), 101)
+ ' ' + (CAST(DATEPART(hh, (SELECT [Value] FROM tbltime WHERE NAME='TIME')) AS VARCHAR)
+ ':' + CAST(DATEPART(mi, (SELECT [Value] FROM tbltime WHERE NAME='TIME')) AS VARCHAR)
+ ':' + CAST(DATEPART(ss, (SELECT [Value] FROM tbltime WHERE NAME='TIME')) AS VARCHAR)
-- unmark the line below if you want to display the miliseconds in the result
--+ '.' + CAST(DATEPART(ms, (SELECT [Value] FROM tbltime WHERE NAME='TIME')) AS VARCHAR)
)
August 29, 2013 at 3:23 pm
It's as easy as this:
SELECT format(getdate(), 'MM/dd/yyyy HH:mm:ss')
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply