Query issue 2

• smharabbani40

  SSC Enthusiast

  Points: 143

  More actions

  August 29, 2013 at 5:55 am

  #279200

  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

  SSC-Dedicated

  Points: 33512

  More actions

  August 29, 2013 at 6:07 am

  #1645935

  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

  SSC-Dedicated

  Points: 33512

  More actions

  August 29, 2013 at 6:11 am

  #1645938

  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

  SSC-Insane

  Points: 24038

  More actions

  August 29, 2013 at 3:23 pm

  #1646159

  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]