Convert character string date and time into DATETIME data type

• SPtrt

  SSChasing Mays

  Points: 603

  More actions

  January 31, 2011 at 6:37 pm

  #233028

  Hi,

  HEre my query it fetched date and time from table and seperate date and time. while i converting it throws "Conversion failed when converting datetime from character string."

  Select CONVERT(varchar(8), ctdate, 112)+

  convert(varchar(2), Datepart(hh, CtDate))+'00'+'00' as CtDate ,

  from Testing

  group by CONVERT(varchar(8), ctdate, 112),

  Datepart(hh, CtDate)

  order by CONVERT(varchar(8), ctdate, 112),

  Datepart(hh, CtDate)

  Result:

  20100528100000

  20100528110000

  HEre i want like this 2010-05-28 10:00:00

  Any help more apprecitable

  Thanks,

• Krystyna Mularczyk

  SSC Enthusiast

  Points: 190

  More actions

  January 31, 2011 at 9:37 pm

  #1280732

  I'm not sure what you are trying to do. If you want to combine date and time into one value, there is no need to convert to string; you can select the date, and add the hours directly using DATEADD().

  But if you want to get the desired output string, you need to change the formatting because style 112 will not give you any delimiters (see http://msdn.microsoft.com/en-us/library/ms187928.aspx).

• SPtrt

  SSChasing Mays

  Points: 603

  More actions

  February 1, 2011 at 12:03 am

  #1280769

  HI,

  Thanks for reply,

  I got the result

  Here i used to convert dateandtime from character string.

  Select [highlight=#ffff11]CONVERT(DATETIME[/highlight],CONVERT(varchar(8), ctdate, 112)+

  convert(varchar(2), Datepart(hh, CtDate))+'00'+'00' ,[highlight=#ffff11]102)[/highlight]as CtDate ,

  from Testing

  group by CONVERT(varchar(8), ctdate, 112),

  Datepart(hh, CtDate)

  order by CONVERT(varchar(8), ctdate, 112),

  Datepart(hh, CtDate)

  Thanks,

• Jeff Moden

  SSC Guru

  Points: 1004339

  More actions

  February 1, 2011 at 7:52 am

  #1280938

  shankarntu (1/31/2011)

  ---

  Hi,

  HEre my query it fetched date and time from table and seperate date and time. while i converting it throws "Conversion failed when converting datetime from character string."

  Select CONVERT(varchar(8), ctdate, 112)+

  convert(varchar(2), Datepart(hh, CtDate))+'00'+'00' as CtDate ,

  from Testing

  group by CONVERT(varchar(8), ctdate, 112),

  Datepart(hh, CtDate)

  order by CONVERT(varchar(8), ctdate, 112),

  Datepart(hh, CtDate)

  Result:

  20100528100000

  20100528110000

  HEre i want like this 2010-05-28 10:00:00

  Any help more apprecitable

  Thanks,

  The error message says it all... you have something in the cDate column that doesn't work out to be a date.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeffrey Williams

  SSC Guru

  Points: 90303

  More actions

  February 1, 2011 at 2:43 pm

  #1281154

  It looks to me like you are trying to round down to the hour for your date column. This is assuming that your column ctDate is a datetime column and not a char/varchar - which it appears to be because you are using convert.

  If that is what you want, this is much simpler than using convert and returns a datetime column:

  SELECT dateadd(hour, datediff(hour, '20110101', t.ctDate), '20110101')

  FROM dbo.Testing t

  GROUP BY dateadd(hour, datediff(hour, '20110101', t.ctDate), '20110101')

  ORDER BY dateadd(hour, datediff(hour, '20110101', t.ctDate), '20110101')

  And, it should perform better than using CONVERT and string manipulation.

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs