[thelist] SQL syntax

[rudy]

> I am trying to setup a view in MS SQL Server that will
> give me all records  where the ExpDate field is equal to
> the last day of the current month.
>
>    WHERE dbo.Member_Info.ExpDate = '2/28/2002'

hi susan

you want the day before the first day of the next month

you have to do this carefully, to cover year boundaries

let's build it up a piece at a time

start with the day of the month for the current date

    datepart(day,getdate())

now take the current date and go one month into the future

    dateadd(month,1,getdate())

finally, subtract the number of days to get the "0th" of the month

    dateadd( day, - datepart(day,getdate()), dateadd(month,1,getdate()) )

convert back to datetime format and insert into your query --

    where dbo.Member_Info.ExpDate =
     convert( datetime,
       dateadd( day, -datepart(day,getdate()), dateadd(month,1,getdate()) )
                  )

so far so good, right?   yes, except you'll get no matching records!!

if ExpDate is really a datetime datatype, be aware that it carries a time
component, and that the actual time values will very likely be
12:00:00:000AM (unless you are storing an expiry time as well -- and this
can easily happen when you first create the value, if you create it using
getdate() to begin with)

however, when you take getdate(), you are actually getting the current time
as well

so when you compare ExpDate to the computed last day of the month, you are
actually comparing times as well

so what you want is to compare just the date portions, excluding the time
portions

since you are seeking equality (and not inequality, e.g. where ExpDate is
greater than the last day of the current month), one safe way is to do the
comparison as character strings

    where convert(char(10),dbo.Member_Info.ExpDate,102) =
         convert(char(10),
       convert( datetime,
       dateadd( day, -datepart(day,getdate()), dateadd(month,1,getdate()) )
                    ) , 102)

caution:  i do not have sql/server, so the above is untested

please let me know if it works, as i'd like to recycle this as a tip some
day...

;o)


rudy
http://rudy.ca/