Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query issue 2 Expand / Collapse
Author
Message
Posted Thursday, August 29, 2013 5:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 13, 2014 7:51 AM
Points: 9, Visits: 23
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
Post #1489656
Posted Thursday, August 29, 2013 6:07 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:34 AM
Points: 2,231, Visits: 2,668
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.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1489665
Posted Thursday, August 29, 2013 6:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:34 AM
Points: 2,231, Visits: 2,668
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)
)



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1489668
Posted Thursday, August 29, 2013 3:23 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 2:28 PM
Points: 786, Visits: 691
It's as easy as this:

SELECT format(getdate(), 'MM/dd/yyyy HH:mm:ss')



Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1489925
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse