tsql retrieve records on date

Question

tsql retrieve records on date

zhtway

Old Hand

Points: 325
More actions
August 21, 2010 at 3:06 am

#222970

Hi,
I wanna retrieve records for today. But not working?
Any idea?
select freedate from holidays where freedate = (getdate())
how can I retrieve for specific date?
rgds,
zhtway

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Dugi SSCoach Points: 17998 More actions · Answer 1

zawhtway (8/21/2010)
Hi,
I wanna retrieve records for today. But not working?
Any idea?
select freedate from holidays where freedate = (getdate())
how can I retrieve for specific date?
rgds,
zhtway

You can do it like this:

SET DATEFORMAT DMY --This date format is for dd/mm/yyyy

SELECT FREEDATE FROM HOLIDAYS WHERE FREEDATE = '01/01/2010' --this is for the specific date

or you can declare the variable that contain current date

DECLARE @CURRENTDATE DATETIME

SET @CURRENTDATE = GETDATE()

SELECT FREEDATE FROM HOLIDAYS WHERE FREEDATE = @CURRENTDATE

😎

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

ColdCoffee

SSC-Dedicated

Points: 39972

August 21, 2010 at 4:11 am

#1210005

Or,

DATEADD(dd, o, DATEDIFF(dd, 0,GETDATE()))

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 3

One more solution to your problem

SELECT * FROM holidays WHERE DATEDIFF( DAY, freedate, GETDATE() ) = 0

The below mentioned article might be helpful to you

http://www.sqlservercentral.com/articles/T-SQL/65806/

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

steve-893342 SSCarpal Tunnel Points: 4439 More actions · Answer 4

Or curiously

SELECT freedate FROM holidays WHERE freedate = {t '00:00:00'}

WayneS SSC Guru Points: 95460 More actions · Answer 5

One thing that all of the above are assuming is that the freedate column contains only dates, with the times set to 00:00:00.000. If this is not the case, then you need to use a range check to do it:

SELECT FREEDATE

FROM HOLIDAYS

WHERE FREEDATE >= DateAdd(day, DateDiff(day, 0, GetDate()), 0)

AND FREEDATE < DateAdd(day, DateDiff(day, 0, GetDate())+1, 0)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Kingston Dhasian (8/22/2010)
One more solution to your problem
SELECT * FROM holidays WHERE DATEDIFF( DAY, freedate, GETDATE() ) = 0
The below mentioned article might be helpful to you
http://www.sqlservercentral.com/articles/T-SQL/65806/

That kills al usefull uses of indexes.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

Kingston Dhasian (8/22/2010)
One more solution to your problem
SELECT * FROM holidays WHERE DATEDIFF( DAY, freedate, GETDATE() ) = 0
The below mentioned article might be helpful to you
http://www.sqlservercentral.com/articles/T-SQL/65806/

Ummm... no. Sorry, Kingston. If you do that, it makes the predicate non sargeable and there's no chance of an index on FreeDate working as an INDEX SEEK.

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

Ah... apologies. I see that Remi already posted such a comment. I've really got to learn to read a whole thread before posting.

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

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 9

Jeff Moden (8/22/2010)
Kingston Dhasian (8/22/2010)
One more solution to your problem
SELECT * FROM holidays WHERE DATEDIFF( DAY, freedate, GETDATE() ) = 0
The below mentioned article might be helpful to you
http://www.sqlservercentral.com/articles/T-SQL/65806/
Ummm... no. Sorry, Kingston. If you do that, it makes the predicate non sargeable and there's no chance of an index on FreeDate working as an INDEX SEEK.

I agree with both of you( Jeff and Remi ). This will surely not use an INDEX SEEK and will kill performance. I will definitely not use this method in stored procedures for applications. I would prefer Wayne's method instead in that case.

But i mostly use this method to write a quick query. It is short and sweet.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

Jeff Moden (8/22/2010)
Ah... apologies. I see that Remi already posted such a comment. I've really got to learn to read a whole thread before posting.

Same here... I had to re-learn that one pretty much every few weeks :w00t:.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

Kingston Dhasian (8/23/2010)
But i mostly use this method to write a quick query. It is short and sweet.

... and slow. Heh... I normally want the highest speed when I need to "write a quick query" because I have to sit there an wait for the results. Also, why practice hitting the wrong notes on the piano just because you don't want to play the full song? 😛 If you always do it right, it'll get easier to always do it right. :w00t:

(Heh... sorry for the mini-rant, Kingston, but you've hit a couple of sore spots with me on this one. 🙂 )

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

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 12

Jeff Moden (8/23/2010)
Kingston Dhasian (8/23/2010)
But i mostly use this method to write a quick query. It is short and sweet.
... and slow. Heh... I normally want the highest speed when I need to "write a quick query" because I have to sit there an wait for the results. Also, why practice hitting the wrong notes on the piano just because you don't want to play the full song? 😛 If you always do it right, it'll get easier to always do it right. :w00t:
(Heh... sorry for the mini-rant, Kingston, but you've hit a couple of sore spots with me on this one. 🙂 )

The term "write a quick query" actually referred to the typing speed and not the execution speed. I never meant to say that the method will be fast in the performance sense.

But you have a point where you said "If you always do it right, it'll get easier to always do it right". It might make me add the non performing code inadvertently into some stored procedure some day. So, point taken:-)

And i am never going to feel bad if you point out my mistakes.:-). So, no problems.

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

zhtway Old Hand Points: 325 More actions · Answer 13

Hi All,

Thanks you guys..

Now I learn alot from all of you.

rgds,

zhtway

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 14

Thanks, Kingston. 🙂

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