Date and Hour Time from DateTime

• AndrewSQLDBA

  SSC-Insane

  Points: 21827

  More actions

  May 22, 2013 at 6:43 am

  #275385

  Hello Everyone

  I am working on a query that I know can be written to be more efficient.

  I have a column that is of datetime data type.

  DECLARE @Dates TABLE

  (

  CreateDate datetime

  )

  INSERT INTO @Dates

  (CreateDate)

  SELECT '2013-03-12 08:44:12.420' UNION ALL -- this one

  SELECT '2013-03-12 08:38:46.103' UNION ALL -- this one

  SELECT '2013-03-12 07:37:17.693' UNION ALL

  SELECT '2013-03-12 07:56:50.697' UNION ALL

  SELECT '2013-03-12 08:43:20.623' UNION ALL -- this one

  SELECT '2013-03-12 08:44:12.297' UNION ALL -- this one

  SELECT '2013-03-12 10:06:57.353' UNION ALL

  SELECT '2013-03-12 09:10:54.390' UNION ALL

  SELECT '2013-03-12 10:03:58.770' UNION ALL

  SELECT '2013-03-12 07:56:59.817'

  I would like to query all the rows for a specific day, '2013-03-12', and only the for the hour of 0800

  If I were to select the count, I should have only 4 rows returned

  Thanks in advance for your assistance, suggestions and comments

  Andrew SQLDBA

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  May 22, 2013 at 6:47 am

  #1617398

  Where Createdate >= '2013-03-12 08:00' and createdate < '2013-03-12 09:00'

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• AndrewSQLDBA

  SSC-Insane

  Points: 21827

  More actions

  May 22, 2013 at 7:33 am

  #1617433

  Thanks Gail

  Greatly appreciate that. Works very nice

  Andrew SQLDBA