Hi All

I have a tricky question wonder if anyone can help.

I store dates in my table as

FDate TDate NoDays


Where Fdate is From Date
TDate is To Date
NoDays is number of days.

We use this to record holidays for our employees.

I would like to write a SQL query which will display all the records where
Fdate or Tdate are within the current month. Not very good at writing SQL
hence need some help.

Some sample data can be like this

Fdate            TDate        NoDays
15/05/04       18/5/04        4
29/04/04        2/5/04        5
29/5/04        3/06/04        4

If that was the data in the my table and running this query I would expect
it include all the three records when searching for month of "May" as all
three records have atleast one day which falls in the month of May

thanks for your help

Jas

[ Post a follow-up to this message ]

Assuming use of an Access database something like this SQL string should
work to get all the FDate's and TDate's within the current month:

strSQL = "SELECT * FROM MyTable WHERE (FDate >= #" &
DateSerial(Year(Date()), Month(Date(), 1) & "#) AND (FDate < #" &
DateSerial(Year(Date()), Month(Date() + 1, 1) & "#) AND (TDate >= #" &
DateSerial(Year(Date()), Month(Date(), 1) & "#) AND (TDate < #" &
DateSerial(Year(Date()), Month(Date() + 1, 1) & "#)"

Best regards,
J. Paul Schmidt, Freelance ASP Web Designer
http://www.Bullschmidt.com
ASP Designer Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[ Post a follow-up to this message ]

select * from table where month(FDate) = month(getdate()) or month(TDate) =
month(getdate())

[ Post a follow-up to this message ]

SQL Server, Access, FoxPro, Sybase, Oracle, DB2, MySQL, Firebird, ...?

Preferably you can do this in the database without worrying about silly date
formatting within ASP.  However, the database vendor, product and version
will be useful information in providing an answer.

--
http://www.aspfaq.com/
(Reverse address to reply.)




"JP SIngh" <none@none.com> wrote in message
news:#HMUzNPXEHA.500@TK2MSFTNGP09.phx.gbl...
> Hi All
>
> I have a tricky question wonder if anyone can help.
>
> I store dates in my table as
>
> FDate TDate NoDays
>
>
> Where Fdate is From Date
> TDate is To Date
> NoDays is number of days.
>
> We use this to record holidays for our employees.
>
> I would like to write a SQL query which will display all the records where
> Fdate or Tdate are within the current month. Not very good at writing SQL
> hence need some help.
>
> Some sample data can be like this
>
> Fdate            TDate        NoDays
> 15/05/04       18/5/04        4
> 29/04/04        2/5/04        5
> 29/5/04        3/06/04        4
>
> If that was the data in the my table and running this query I would expect
> it include all the three records when searching for month of "May" as all
> three records have atleast one day which falls in the month of May
>
> thanks for your help
>
> Jas
>
>

[ Post a follow-up to this message ]

Using Access 2000

thanks


"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:ePcrRsRXEHA.1356@TK2MSFTNGP09.phx.gbl...
> SQL Server, Access, FoxPro, Sybase, Oracle, DB2, MySQL, Firebird, ...?
>
> Preferably you can do this in the database without worrying about silly
date
> formatting within ASP.  However, the database vendor, product and version
> will be useful information in providing an answer.
>
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
>
>
> "JP SIngh" <none@none.com> wrote in message
> news:#HMUzNPXEHA.500@TK2MSFTNGP09.phx.gbl... 
where[vbcol=seagreen] 
SQL[vbcol=seagreen] 
expect[vbcol=seagreen] 
all[vbcol=seagreen] 
>
>

[ Post a follow-up to this message ]