how search BETWEEN the first day of the month and Last day

Question

Post reply

how search BETWEEN the first day of the month and Last day

midan1

SSCertifiable

Points: 5100
More actions
November 23, 2005 at 3:59 pm

#188608

need help
how to search between the first day of the month AND the last day of the month
and match to all the years
HOW ??
thnks
ilan

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

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

midan1 SSCertifiable Points: 5100 More actions · Answer 1

i think i found

the first day of the month

select DATEADD(m,DATEDIFF(m,0,GETDATE()),0)

the last dat of the month

SELECT DateAdd(day,-1,DateAdd(month,DateDiff(month,0,getdate())+1,0))

Sergiy SSC Guru Points: 110192 More actions · Answer 2

By

SELECT DateAdd(day,-1,DateAdd(month,DateDiff(month,0,getdate())+1,0))

you miss last day of the month

SELECT DateAdd(ms,-3,DateAdd(month,DateDiff(month,0,getdate())+1,0))

will be better

_____________
Code for TallyGenerator

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Since you already have a good solution for the first day in a month with

select DATEADD(m,DATEDIFF(m,0,GETDATE()),0)

Why not use this to get the last day in a month, too?

select DATEADD(m,DATEDIFF(m,0,GETDATE()),31)

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Instead of messing around with milliseconds and adding 31 days, etc.... it's a takeoff on what the other posters have done but a wee bit different... works kinda like Frank's does and includes the final milliseconds of the last day of the month.

SELECT somedatecol FROM sometable WHERE somedatecol >= DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) AND somedatecol < DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)

--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)
Intro to Tally Tables and Functions

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

...and finally...

SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)

, DATEADD(mm,DATEDIFF(mm,-1,GETDATE()),0)

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Oh, btw, Jeff, I'm not adding 31 days. I only use a different base date.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

midan1 SSCertifiable Points: 5100 More actions · Answer 7

OK

but i need to search between dates

like this i can not !!!

SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)

, DATEADD(mm,DATEDIFF(mm,-1,GETDATE()),0)

i need to search from

first day of the month

the-1

and between

last

28 OR 29 OR 30 OR 31

not from 1.11.2005 and 1.12.2005

thnks

Ric Sierra SSCrazy Points: 2260 More actions · Answer 8

Easy...

declare @YY as varchar(4), @mm as varchar(2)

set @YY = '2000'

set @mm = '12'

select col1, col2, datecol, ...

from tablename

where DAY(datecol) between 1 and DAY(DATEADD(Month, 1, CONVERT(DATETIME, @YY + '-' + @mm + '-01'))-1)

In this example the where clause means:

...where day(datecol) between 1 and 31

remember!...Febrary is a tricky month that's the reason for use the year as part of parameters.

You can use DATEPART(dd, datecol) or DAY(datecol)

Hello from Guatemala

Ricardo

Sergiy SSC Guru Points: 110192 More actions · Answer 9

Never use

where DAY(datecol) between !!!

Unless your table not gonna exceed 100 rows.

This option of WHERE clause eliminates all indexes, it gonna be full table scan.

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110192 More actions · Answer 10

I told you to search

between 1.11.2005 and 3ms before 1.12.2005.

What's wrong with this approach?

Another way is

>= 1.11.2005 and < 1.12.2005

Why you don't like these?

And using these options you don't need to cut off time portion of datetime. Index will be used in full.

_____________
Code for TallyGenerator

Ric Sierra SSCrazy Points: 2260 More actions · Answer 11

Yes!... you right

Ok... add a new attribute with the day of the date and index it.

I think the WHERE is more complex than that (is only a part of the where clause). Think this: What is the sense of filter a table with a range of 1 to 31 or 1 to 28 in the first case is all the rows and in the other case is almost all the rows.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 12

midan1,

not to sound offending here, but I remember several threads started by you about this issue or *very* similar issues. Haven't all the answers you got there helped?

Please think about your exact requirements *before* you post and please express them as clearly as you can. And, as sometimes code is more than a million words, checck this out http://www.aspfaq.com/etiquette.asp?id=5006 With all the information provided that are asked for in that link, it's likely you get a good solution quickly. Without clear informations it's almost impossible to guess what you really want and a waste of time for those who think about your problems and are trying to help you.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 13

Midan1,

YOU WROTE:

----------------------------------------------------------------------------------------but i need to search between dates

like this i can not !!!

SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)

, DATEADD(mm,DATEDIFF(mm,-1,GETDATE()),0)

i need to search from

first day of the month

the-1

and between

last

28 OR 29 OR 30 OR 31

not from 1.11.2005 and 1.12.2005

thnks"

----------------------------------------------------------------------------------------

To add to Frank's post... my previous post (as well as Frank's and a couple of others who posted) does search from the first day of the current month to the last whether the current month has 28, 29, 30, or 31 days... you didn't even try it before you flipped you lid... so try it! And, pay attention! It say's that somedatecol is LESS THAN the first day of next month! Here it is again...

SELECT somedatecol FROM sometable WHERE somedatecol >= DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) AND somedatecol < DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)

--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)
Intro to Tally Tables and Functions

Harley Arnett-275660 Right there with Babe Points: 749 More actions · Answer 14

First, I would just like to say thanks to all the great people who donate their time to answering questions - your help is invaluable.

Jeff,

Clever solution to the first and last date calc - I do think that there may be an error though in the last day of the month calc - I believe the +1 should be on the outside of the DATEDIFF parens....

SELECT somedatecol
  FROM sometable
 WHERE somedatecol >= DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
   AND somedatecol <  DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)

This will add a month to the month calc - not a day to the getdate calc.

Regards,

Harley