ASP Dates Display

[JP SIngh]

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

[Bullschmidt]

Re: ASP Dates Display

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!

[Gervin]

Re: ASP Dates Display

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

[Rags]

ASP Dates Display

Hi

Use this query

Select * from <your table>
Where Month(TDate)=5 and Month(FDate)=5

Regards
Rags


[color=blue]
>-----Original Message-----
>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[/color]
the records where[color=blue]
>Fdate or Tdate are within the current month. Not very[/color]
good at writing SQL[color=blue]
>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[/color]
query I would expect[color=blue]
>it include all the three records when searching for month[/color]
of "May" as all[color=blue]
>three records have atleast one day which falls in the[/color]
month of May[color=blue]
>
>thanks for your help
>
>Jas
>
>
>.
>[/color]

[Aaron [SQL Server MVP]]

Re: ASP Dates Display

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.co m> wrote in message
news:#HMUzNPXEH A.500@TK2MSFTNG P09.phx.gbl...[color=blue]
> 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
>
>[/color]

[JP SIngh]

Re: ASP Dates Display

Using Access 2000

thanks


"Aaron [SQL Server MVP]" <ten.xoc@dnartr eb.noraa> wrote in message
news:ePcrRsRXEH A.1356@TK2MSFTN GP09.phx.gbl...[color=blue]
> SQL Server, Access, FoxPro, Sybase, Oracle, DB2, MySQL, Firebird, ...?
>
> Preferably you can do this in the database without worrying about silly[/color]
date[color=blue]
> 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.co m> wrote in message
> news:#HMUzNPXEH A.500@TK2MSFTNG P09.phx.gbl...[color=green]
> > 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[/color][/color]
where[color=blue][color=green]
> > Fdate or Tdate are within the current month. Not very good at writing[/color][/color]
SQL[color=blue][color=green]
> > 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[/color][/color]
expect[color=blue][color=green]
> > it include all the three records when searching for month of "May" as[/color][/color]
all[color=blue][color=green]
> > three records have atleast one day which falls in the month of May
> >
> > thanks for your help
> >
> > Jas
> >
> >[/color]
>
>[/color]