Date Diff - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Date Diff

Author

Topic

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-01 : 03:54:28

Hi,

startdate column(DateTime Field) pl see below..

Startdate
2010-02-24 00:00:00.000
2010-02-25 00:00:00.000
2010-02-26 00:00:00.000
2010-02-26 00:00:00.000
2010-03-01 00:00:00.000

1.I want to find only Today's date:

output:
2010-03-01 00:00:00.000

2.Do i have to create the index for this column(StartDate here)?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-01 : 03:59:11


where startdate >= dateadd(day, datediff(day, 0, getdate()), 0)
and   startdate <  dateadd(day, datediff(day, 0, getdate()), 1)

2. It is up to you. But index will increase the performance of the query

KH
[spoiler]Time is always against us[/spoiler]

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-01 : 04:06:57

Hi,
Thank you very much for your immediate reply..working as expected..

what about this?
DATEDIFF(dd,getdate(),startdate)<=0

it's also giving the result..

hey,don't mistake me please..

could you please explain me above..you u haven't suugest this way..
could you please help me.what is wrong this

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-01 : 04:10:29

quote:
DATEDIFF(dd,getdate(),startdate)<=0

it's also giving the result..

but SQL Server will not be able to utilize any index on column startdate (if you have one)

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-01 : 04:11:12

quote:
Originally posted by haroon2k9

Hi,
Thank you very much for your immediate reply..working as expected..

what about this?
DATEDIFF(dd,getdate(),startdate)<=0

it's also giving the result..

hey,don't mistake me please..

could you please explain me above..you u haven't suugest this way..
could you please help me.what is wrong this

This will not make use of index

Madhivanan

Failing to plan is Planning to fail

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-01 : 04:12:42

quote:

Originally posted by khtan

1.


where startdate >= dateadd(day, datediff(day, 0, getdate()), 0)
and   startdate <  dateadd(day, datediff(day, 0, getdate()), 1)

For this ..SQL Server will be able to utilize any index on column startdate?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-01 : 04:14:57

yes.

Because, in your method, you are applying a function on your column that SQL Server will need to scan all rows to determine the result before comparing with 0

KH
[spoiler]Time is always against us[/spoiler]

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-01 : 04:15:46

quote:
Originally posted by khtan

yes

KH
[spoiler]Time is always against us[/spoiler]

Thank you very much khtan for your explanation.will follow

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-01 : 04:16:33

quote:
Originally posted by madhivanan

quote:
Originally posted by haroon2k9

Hi,
Thank you very much for your immediate reply..working as expected..

what about this?
DATEDIFF(dd,getdate(),startdate)<=0

it's also giving the result..

hey,don't mistake me please..

could you please explain me above..you u haven't suugest this way..
could you please help me.what is wrong this

This will not make use of index

Madhivanan

Failing to plan is Planning to fail

Hi madhi,
Thank you very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources