[thelist] SQL syntax

[Scott Schrantz]

> -----Original Message-----
> From: Susan Wallace [mailto:susanhw at webcastle.com]
>
> 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??

If you're using MS SQL, are you programming in VBScript? VBS doesn't have
anything as elegant as DaysInMonth (that I know of), but you can make one
with DateDiff.

<%
Function DaysInMonth(byVal CheckDate)

	DaysInMonth = DateDiff("d", Month(CheckDate) & "/01/" &
Year(CheckDate), DateAdd("m", 1, Month(CheckDate) & "/01/" &
Year(CheckDate)))

End Function

ExpDate = Month(Date()) & "/" & DaysInMonth(Date()) & "/" & Year(Date())
%>

This counts the number of days between the first of this month and the first
of next month to get the number of days in this month. Then it concatenates
them into ExpDate, which you can use in your SQL:
"...WHERE dbo.Member_Info.ExpDate = #" & ExpDate "#;"
Which would be
WHERE dbo.Member_Info.ExpDate = #2/28/2002# **

Messy, but it should work.

** Not sure if MSSQL needs the # # around dates like Access does.