[thelist] SQL syntax

[Warden, Matt]

On Feb 1, Susan Wallace had something to say about [thelist] SQL syntax

>This may be an overly simple question, but I've stared at this too long.
>
>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.
>
>For example,
>
>WHERE dbo.Member_Info.ExpDate = '2/28/2002'
>
>This is fine if I want to edit this by hand each month to get the last day
>of the month, but what I would like to do is set it up so that the value
>that ExpDate is equal to is always the last day of the current month.
>
...
>If I were setting the ExpDate field to the last day of February, one year
>from now, I would use this syntax
>:
><CFSET ExpDate = "#Month(CheckDate)#" & "-" & "#DaysInMonth(CheckDate)#" &
>"-" & "#Year(CheckDate)#">
>
>What is the syntax for setting that up in a view on mssql??
>
>The ExpDate field is a Date/Time type, this is a MS SQL 7 server.


This may not be the BEST solution, but you could have a table with:

month 	lastdate
1	31
2	28
...

then select the day based on the current month, which is available from a
function.

hope this helps a bit,

--
mattwarden
mattwarden.com