Getting date + Filter it !!! how ???

Question

Post reply

Getting date + Filter it !!! how ???

karthik M

One Orange Chip

Points: 29139
More actions
March 22, 2010 at 6:38 am

#218430

Hi All,
I have a scenario.
I have table like
create table dt
(
dt datetime
)
select * from dt
insert into dt
select '01/jan/2010'
union
select '03/jan/2010'
union
select '04/jan/2010'
union
select '31/jan/2010'
union
select '01/feb/2010'
union
select '03/feb/2010'
union
select '04/feb/2010'
union
select '28/feb/2010'
I would like to filter the records whereas the date is not fall into month end date.
expected output:
01/jan/2010
03/jan/2010
04/jan/2010
01/feb/2010
03/feb/2010
04/feb/2010
karthik

Viewing 8 posts - 1 through 7 (of 7 total)

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

steveb. SSC-Forever Points: 46733 More actions · Answer 1

you can use

SELECT dt FROM Dt

WHERE (MONTH(dt) IN (1,3,5,7,8,10,12) AND DAY(dt) <31 )

OR (MONTH(dt) IN (4,6,9,11) AND DAY(dt) <30 )

OR (MONTH(dt) = 2 AND DAY(dt) < 28)

ORDER BY dt

this query should work but is not the most effficent way,

for a more scalable and efficent solution I would use a calender table

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 2

SELECT*

FROMdt

WHEREDAY( DATEADD( DAY, 1, dt ) ) != 1

I don't think performance should be a concern as almost all the rows ( hopefully ) are returned. In such a case there is not much we can do to improve the performance.

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/

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

Kingston Dhasian (3/22/2010)

WHEREDAY( DATEADD( DAY, 1, dt ) ) != 1 i am agree with dhasian but if we talk about heavy/large table definitely above code would kill seek operation.By the way good/tricky logical in Where clause

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

steveb. SSC-Forever Points: 46733 More actions · Answer 4

Kingston Dhasian (3/22/2010)
SELECT* FROMdt
WHEREDAY( DATEADD( DAY, 1, dt ) ) != 1
I don't think performance should be a concern as almost all the rows ( hopefully ) are returned. In such a case there is not much we can do to improve the performance.

actually this is a lot better than my example and handles leap-years as well, nice..

The perfomance issue comes from having a non SARGable query, which a calender table would solve

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 5

karthikeyan-444867 (3/22/2010)
Hi All,
I have a scenario.
I have table like
create table dt
(
dt datetime
)
select * from dt
insert into dt
select '01/jan/2010'
union
select '03/jan/2010'
union
select '04/jan/2010'
union
select '31/jan/2010'
union
select '01/feb/2010'
union
select '03/feb/2010'
union
select '04/feb/2010'
union
select '28/feb/2010'
I would like to filter the records whereas the date is not fall into month end date.
expected output:
01/jan/2010
03/jan/2010
04/jan/2010
01/feb/2010
03/feb/2010
04/feb/2010

Will any of those dates ever have a time associated with them? That's a bit of a rhetorical question... you should always assume they will and write the code as if they will so that when someone throws a time in by mistake or by intention, you won't have to change the code.

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

Jeff Moden (3/22/2010)
Will any of those dates ever have a time associated with them? That's a bit of a rhetorical question... you should always assume they will and write the code as if they will so that when someone throws a time in by mistake or by intention, you won't have to change the code.

Yes, you are right and some times we have frontend developers( some of them ) who feel just too lazy to strip the time part in the Date Column before inserting them into a table even after clear instructions about the same.:angry: So it is always better we think of all such possibilities before coding.

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/

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 7

steveb. (3/22/2010)
Kingston Dhasian (3/22/2010)
SELECT* FROMdt
WHEREDAY( DATEADD( DAY, 1, dt ) ) != 1
I don't think performance should be a concern as almost all the rows ( hopefully ) are returned. In such a case there is not much we can do to improve the performance.
actually this is a lot better than my example and handles leap-years as well, nice..
The perfomance issue comes from having a non SARGable query, which a calender table would solve

Thanks for the appreciation Steve. But what i thought was that if we were to return say 29 out of the 30 rows the optimizer will most probably go for a scan rather than a seek. But as Bhuvnesh also said it depends on the data and its distribution. We will have to test with the appropriate data to know whether the index will be properly used.

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/