|
Home > Archive > IIS ASP > June 2004 > ASP Dates Display
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| JP SIngh 2004-06-28, 8:55 am |
| 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 2004-06-28, 8:55 am |
| 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 2004-06-28, 8:55 am |
| select * from table where month(FDate) = month(getdate()) or month(TDate) =
month(getdate())
| |
| Aaron [SQL Server MVP] 2004-06-28, 8:55 am |
| 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
>
>
| |
| JP SIngh 2004-06-29, 9:56 am |
| 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]
>
>
|
|
|
|
|