Need assistance to CAST the StartDate

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  October 1, 2012 at 7:45 am

  #1543977

  t.brown 89142 (10/1/2012)

  ---

  GETDATE() is just a function that returns today's date, as an example datetime.

  You would substitute your date column names

  SELECT CAST(CONVERT(VARCHAR(10), StartDate, 120) + ' 12:00:00' AS DATETIME)

  If you only need StartDates the same as endDate, then use a where condition

  WHERE CONVERT(VARCHAR(10), startDate, 120) = CONVERT(Varchar(10), endDate, 120)

  Seriously... get out of the habit of using CONVERT to VARCHAR to do this stuff. It's slower than just about any other method for stripping or adding time.

  --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)
  

• t.brown 89142

  SSCommitted

  Points: 1530

  More actions

  October 1, 2012 at 7:54 am

  #1543986

  Jeff Moden (10/1/2012)

  ---

  t.brown 89142 (10/1/2012)

  ---

  GETDATE() is just a function that returns today's date, as an example datetime.

  You would substitute your date column names

  SELECT CAST(CONVERT(VARCHAR(10), StartDate, 120) + ' 12:00:00' AS DATETIME)

  If you only need StartDates the same as endDate, then use a where condition

  WHERE CONVERT(VARCHAR(10), startDate, 120) = CONVERT(Varchar(10), endDate, 120)

  Seriously... get out of the habit of using CONVERT to VARCHAR to do this stuff. It's slower than just about any other method for stripping or adding time.

  I consider my wrist slapped O Guru. :blush: Promise not to do it again 😉

• Mac1986

  SSCertifiable

  Points: 6216

  More actions

  October 1, 2012 at 11:01 am

  #1544062

  Hi All, thanks for the inputs..

  SELECT StartDateTime=DATEADD(day, DATEDIFF(day, 0, WQ.WorkQueueEndWorkDate), 0),WQ.WorkQueueEndWorkDate as 'EndDateTime',

  DATEDIFF(Hour, DATEADD(day, DATEDIFF(day, 0.0, WQ.WorkQueueEndWorkDate), 0.0), WQ.WorkQueueEndWorkDate) as 'RunTime(Hours)'

  This works now. But not quite optimal quite compared to your High TSQL skills. If I could get some decimal points in the RunTime(Hours) column, my problem is completely solved.

  StartDateTime EndDateTime RunTime(Hours)

  2012-09-30 00:00:00.0002012-09-30 12:51:05.17012

  2012-09-29 00:00:00.0002012-09-29 16:43:45.98716

  2012-09-28 00:00:00.0002012-09-28 20:23:33.34320