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

  • 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

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

  • 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

  • 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]

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

  • ...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]

  • 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]

  • 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

  • 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

  • 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

  • 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

  • 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.

  • 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]

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

  • 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