Results 1 to 7 of 7

Thread: [Resolved] SQL, Date & Right

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    May 2000
    Location
    England
    Posts
    135

    [Resolved] SQL, Date & Right

    Hi,

    I know there are a few tips like using # with dates and SQL and ASP.

    Basically I have a date, say 10/01/2001 and I am trying to select all records where right(recorddate,4='2001'.

    For some reason it will not return any records, but it will when I leave this part of the query out.

    Any solutions would be much appreciated.
    Cheers,
    Sparky.
    Last edited by Sparky; Sep 16th, 2004 at 03:16 PM.

  2. #2
    Big D Danial's Avatar
    Join Date
    Jul 2000
    Location
    ASP.Net Forum
    Posts
    2,877

    Re: SQL, Date & Right

    Originally posted by Sparky
    Hi,

    I know there are a few tips like using # with dates and SQL and ASP.

    Basically I have a date, say 10/01/2001 and I am trying to select all records where right(recorddate,4='2001'.

    For some reason it will not return any records, but it will when I leave this part of the query out.

    Any solutions would be much appreciated.
    Cheers,
    Sparky.
    If the field in the Database is stored as Date then you can not use Right command. You will have to use SQL's Between keyword.

    e.g

    Access
    Code:
    Select * From MyTable Where RecordDate Between #1/1/2001# and #31/12/2001#
    SQL Server
    Code:
    Select * From MyTable Where RecordDate Between '1/1/2001' and '31/12/2001'
    [VBF RSS Feed]

    There is a great war coming. Are you sure you are on the right side? Atleast I have chosen a side.

    If I have been helpful, Please Rate my Post. Thanks.

    This post was powered by :

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    May 2000
    Location
    England
    Posts
    135

    Cheers

    Thanks very much - perfect! I love it when the answer is that short and simple.

    Cheers,
    Sparky.

  4. #4
    Lively Member
    Join Date
    Sep 2004
    Posts
    96

    Re: Re: SQL, Date & Right

    Originally posted by Danial
    If the field in the Database is stored as Date then you can not use Right command. You will have to use SQL's Between keyword.

    e.g

    Access
    Code:
    Select * From MyTable Where RecordDate Between #1/1/2001# and #31/12/2001#
    SQL Server
    Code:
    Select * From MyTable Where RecordDate Between '1/1/2001' and '31/12/2001'
    Poppy-cock!

    Code:
    Select * From MyTable Where YEAR(RecordDate) = 2004
    Works just as well (I *think* Access has this too.)

    As a side note: the between doesn't work effectively if times are stored in the field as well. 12/31/2004 14:35:00.000 wouldn't get picked up using Between.

  5. #5
    Big D Danial's Avatar
    Join Date
    Jul 2000
    Location
    ASP.Net Forum
    Posts
    2,877

    Re: Re: Re: SQL, Date & Right

    Originally posted by TalonSoftware
    Poppy-cock!
    EXCUSE ME?

    Year might work in MS SQL Server and Access but I do not know if Year is a valid in SQL.

    Not sure what you mean by, "it wouldnt get picket up if you have time in the filed"
    [VBF RSS Feed]

    There is a great war coming. Are you sure you are on the right side? Atleast I have chosen a side.

    If I have been helpful, Please Rate my Post. Thanks.

    This post was powered by :

  6. #6
    Lively Member
    Join Date
    Sep 2004
    Posts
    96
    Geez, don't get yer panties in a twist man.... geeeez.
    It was in regards to the comment "You will have to use SQL's Between keyword." I was simply trying to point out there was an alternative, as well as the pitfalls of using the between keyword. And I did note that I wasn't sure that it was avail in Access or not, and even if not, the Format function is, and that can be used to extract the year.

    OK, here's an example. To keep it simple, 5 rows:
    1/1/2004 12:00:00 <- Jan 1, 2004 at Noon
    5/14/2004 15:15:00 <- May 14, at 3:15 PM
    4/5/2003 4:50:36 <- April 5, 2003 at 4:50 AM
    12/31/2004 00:00:00 <- Dec 31, 2004 midnight
    12/31/2004 05:00:00 <- Dec 31, 2004 5:00 AM

    If I select from this table WHERE myDate Between #1/1/2004# AND #12/31/2004# I will get 3 rows:

    1/1/2004 12:00:00 <- Jan 1, 2004 at Noon
    5/14/2004 15:15:00 <- May 14, at 3:15 PM
    12/31/2004 00:00:00 <- Dec 31, 2004 midnight

    That's because 12/31/2004 05:00:00 happens AFTER #12/31/2004# and so gets excluded from the results.
    That's all, nothing more, nothing less.

    What an abso'fricin'lutly wonderful welcome that was.

  7. #7
    Big D Danial's Avatar
    Join Date
    Jul 2000
    Location
    ASP.Net Forum
    Posts
    2,877
    Geez, don't get yer panties in a twist man.... geeeez.


    Learn some manner.

    What an abso'fricin'lutly wonderful welcome that was.
    With a manner and attitude like that what kind of reception do you expect?
    [VBF RSS Feed]

    There is a great war coming. Are you sure you are on the right side? Atleast I have chosen a side.

    If I have been helpful, Please Rate my Post. Thanks.

    This post was powered by :

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width