SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query issue 2


Query issue 2

Author
Message
smharabbani40
smharabbani40
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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
HanShi
HanShi
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4380 Visits: 3668
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’! **
HanShi
HanShi
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4380 Visits: 3668
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’! **
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2111 Visits: 872
It's as easy as this:

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



Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search