ASP Dates Display

Discussion in 'ASP General' started by JP SIngh, Jun 28, 2004.

  1. JP SIngh

    JP SIngh Guest

    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, Jun 28, 2004
    #1
    1. Advertising

  2. JP SIngh

    Bullschmidt Guest

    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!
     
    Bullschmidt, Jun 28, 2004
    #2
    1. Advertising

  3. JP SIngh

    Gervin Guest

    select * from table where month(FDate) = month(getdate()) or month(TDate) =
    month(getdate())
     
    Gervin, Jun 28, 2004
    #3
  4. JP SIngh

    Rags Guest

    Hi

    Use this query

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

    Regards
    Rags



    >-----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

    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
    >
    >
    >.
    >
     
    Rags, Jun 28, 2004
    #4
  5. 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" <> wrote in message
    news:#...
    > 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
    >
    >
     
    Aaron [SQL Server MVP], Jun 28, 2004
    #5
  6. JP SIngh

    JP SIngh Guest

    Using Access 2000

    thanks


    "Aaron [SQL Server MVP]" <> wrote in message
    news:...
    > 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" <> wrote in message
    > news:#...
    > > 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, Jun 29, 2004
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. David Lozzi

    Dates dates dates dates... SQL and ASP.NET

    David Lozzi, Sep 29, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    904
    Rob Schieber
    Sep 30, 2005
  2. PW

    Dates! Dates! Dates!

    PW, Aug 7, 2004, in forum: ASP General
    Replies:
    4
    Views:
    231
    Mark Schupp
    Aug 9, 2004
  3. Replies:
    1
    Views:
    236
    Jano Svitok
    Jul 17, 2007
  4. kellygreer1

    RFC-822 dates into Ruby dates

    kellygreer1, Jun 8, 2008, in forum: Ruby
    Replies:
    1
    Views:
    255
    Eric I.
    Jun 8, 2008
  5. Dr J R Stockton

    FAQ Dates; Opera Dates.

    Dr J R Stockton, Jun 18, 2007, in forum: Javascript
    Replies:
    0
    Views:
    188
    Dr J R Stockton
    Jun 18, 2007
Loading...

Share This Page