November 23, 2005 at 3:59 pm
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
November 23, 2005 at 5:26 pm
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))
November 23, 2005 at 6:09 pm
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
November 24, 2005 at 1:34 am
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]
November 24, 2005 at 1:42 am
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
Change is inevitable... Change for the better is not.
November 24, 2005 at 1:44 am
...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]
November 24, 2005 at 1:46 am
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]
November 24, 2005 at 8:17 am
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
November 24, 2005 at 11:14 am
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
November 24, 2005 at 1:37 pm
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
November 24, 2005 at 1:43 pm
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
November 24, 2005 at 2:26 pm
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.
November 25, 2005 at 4:09 am
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]
November 25, 2005 at 1:15 pm
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
Change is inevitable... Change for the better is not.
November 28, 2005 at 3:13 pm
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply