Post reply

Date stuff

  • July 18, 2007 at 1:39 pm

    #120753

    I need some help with a date. 

    Here is the scenaro:

    I have a stored proceedure that will have 1 variable (datetime)

    The select statement works fine.  What I need to figure out is how to contrain the data based on 2 things

    1. The variable information for instance 01/15/2007 and

    2  The month that day falls in

    so the end result need to be select all record with the criteria of @startdate and (the month that falls in) 

    Any help would be greatly appreciated. 

  • July 18, 2007 at 1:47 pm

    #720522

    I'm just a little confused, what is the criteria for select records again?  My guess is you are looking for all records in the current month based on the value in @startdate, for instance: @startdate = '2007-07-18', all records in '2007-07-%'; correct?

     

  • July 18, 2007 at 1:49 pm

    #720523

    That is correct

  • July 18, 2007 at 1:56 pm

    #720526

    Try this:

    where

    datefield >= dateadd(mm, datediff(mm,0,@startdate), 0) and datefield < dateadd(mm, 1, dateadd(mm, datediff(mm,0,@startdate), 0))

  • July 18, 2007 at 1:58 pm

    #720527 

    declare @StartDate datetime
set @StartDate = '20070718'
    Select
 *
from
 MyTable
where
 -- Greater than or equal start of month
 MyDate >= dateadd(mm,datediff(mm,0,@StartDate),0) and
 -- Less than start of next month
MyDate < dateadd(mm,datediff(mm,0,@StartDate)+1,0)

  • July 18, 2007 at 2:04 pm

    #720531

    Michael, I knew there had to be another way to get the upper bound for the where clause.

     

  • July 18, 2007 at 2:12 pm

    #720535

    Thank you very much Lynn and Michael.  It worked perfectly. Kudo's to both

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

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