Query on time part

• moorthy.jy

  SSCommitted

  Points: 1753

  More actions

  July 2, 2009 at 2:53 am

  #95813

  Please help me to write the statement on the following condition.

  we have transaction table with TranDate (datetime column) which stores YYYY-MM-DD HH:MM:SS:MSS format.

  I want to filter rows on trandate column for particular time, say 05:41 or 13:55 or 19.12

  select columnname from table where datepart(mm, TranDate) in ('5:41' or '13:55' or '19.12') ????????????

  Thanks

• t.hitendra

  SSCommitted

  Points: 1620

  More actions

  July 2, 2009 at 3:23 am

  #1018986

  Hi there,

  This is a way of filtering based on time

  create table #tmp(nos int,TranDate datetime)

  insert into #tmp

  select 1,'2009-07-02 05:41:00.000' union all

  select 2,'2009-07-02 20:12:00.000' union all

  select 3,'2009-07-02 14:47:00.000' union all

  select 4,'2009-07-02 16:50:00.000' union all

  select 5,'2009-07-02 13:55 :00.000' union all

  select 6,'2009-07-02 14:47:00.000' union all

  select 7,'2009-07-02 19:12:00.000'

  select * from #tmp where convert(varchar(5),TranDate,108) in ('05:41','13:55','19:12')

  drop table #tmp

  You can clarify if something is missing here.

• John H Marsh

  SSChampion

  Points: 11671

  More actions

  July 2, 2009 at 3:31 am

  #1018990

  Hello,

  You could also consider using DateDiff (Please see below). How accurate do you need the comparison to be e.g. to the nearest minute, and are you storing Milliseconds in the column?

  Select

  DateDiff(s, GetDate(), GetDate()) As ExactMatch,

  DateDiff(s, GetDate(), DateAdd(ms, 999, GetDate())) As MillisecondsCauseDifference

  Regards,

  John Marsh

  www.sql.lu
  SQL Server Luxembourg User Group

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  July 2, 2009 at 8:28 am

  #1019189

  You might be best serverd by adding a computed column to the table that contains the number of seconds (milliseconds if you need this fine a granularity) and then using that to do your matching.

  Something like:

  create table #tmp

  (

  nos int,

  TranDate DATETIME,

  time_of_day_seconds AS DATEDIFF(second,

  dateadd(day, datediff(day, 0, trandate),

  0), trandate)

  )

  insert INTO #tmp

  select

  1,

  '2009-07-02 05:41:00.000'

  union all

  select

  2,

  '2009-07-02 20:12:00.000'

  union all

  select

  3,

  '2009-07-02 14:47:00.000'

  union all

  select

  4,

  '2009-07-02 16:50:00.000'

  union all

  select

  5,

  '2009-07-02 13:55 :00.000'

  union all

  select

  6,

  '2009-07-02 14:47:00.000'

  union all

  select

  7,

  '2009-07-02 19:12:00.000'

  union all

  select

  2,

  '2009-07-01 05:41:00.000'

  union all

  select

  2,

  '2009-06-01 05:41:00.000'

  SELECT

  *

  FROM

  #tmp AS T

  WHERE

  time_of_day_seconds = DATEDIFF(second,

  dateadd(day, datediff(day, 0, '1900-01-01'),

  0), '1900-01-01 05:41:00.000')

  DROP TABLE #tmp

  Also here's a link to a nice list of common date routines. I reference this post regularly

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• ss-457805

  SSCertifiable

  Points: 5872

  More actions

  July 2, 2009 at 9:26 am

  #1019242

  Thanks Jack. That's a very useful link. I have added that to my favourites. 🙂

  blog: http://sarveshsingh.comTwitter: @sarveshsing

• moorthy.jy

  SSCommitted

  Points: 1753

  More actions

  July 3, 2009 at 2:00 pm

  #1019859

  Thanks everyone. hitendra's reply suits me best. thanks again.

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  July 3, 2009 at 2:29 pm

  #1019866

  Just be aware that the solution you have selected will not scale well as it can at best do a index scan because of the function being used on the column in the where clause.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• moorthy.jy

  SSCommitted

  Points: 1753

  More actions

  July 3, 2009 at 2:59 pm

  #1019870

  Jack,

  thanks for the warning. this is a 3rd party app. and we are restricted by design. what we can do is adding few indexes as necessary.