Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2003
    Posts
    6

    Question Unanswered: Filtering by date

    This may be a dumb question but if my database has a field called order_date and the dates in that field are displayed as "2001/03/23" how can I order by date (im trying to filter out all but the month march)?

    heres what I have so far (its not working):

    SELECT O.ORDER_DATE, R.TITLE, OL.QUANTITY, OL.ORDER_PRICE
    FROM ORDERS O, ORDERLINE OL, RECORDING R
    WHERE O.ORDER_NUMBER = OL.ORDER_NUMBER AND OL.RECORDING_ID = R.RECORDING_ID
    AND O.ORDER_DATE >= 03/01/2001 and O.ORDER_DATE < 04/01/2001
    ORDER BY R.TITLE, O.ORDER_DATE


    Please help me,

    Thank you very much

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    This might work
    Code:
    SELECT 
    	O.ORDER_DATE, 
    	R.TITLE, 
    	OL.QUANTITY, 
    	OL.ORDER_PRICE
    FROM 
    	ORDERS O, 
    	ORDERLINE OL, 
    	RECORDING R
    WHERE 
    	O.ORDER_NUMBER = OL.ORDER_NUMBER 
    	AND OL.RECORDING_ID = R.RECORDING_ID
    	AND convert(varchar(10),O.ORDER_DATE,101) between '03/01/2001' and  '03/31/2001'
    ORDER BY 
    	R.TITLE, 
    	O.ORDER_DATE
    Last edited by Enigma; 11-17-03 at 17:49.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Post the table ddl, but I'm guessing that you're "date" column is defined as char or varchar...

    You'll probably need to CONVERT it to datetime and then compare

    CONVERT(datetime, ORDER_DATE) >=...

    kinda thing...

    Hope you don't have a lot of rows....it's going to scan the table...

    EDIT: Sniped
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Oct 2003
    Posts
    6
    Thanks so much that one works beautifully, but what exactly does this line do?

    "AND convert(varchar(10),O.ORDER_DATE,101) between '03/01/2001' and '03/31/2001' "

    I am just trying to understand it so that I may learn from my mistakes.


    Thank you!

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The server does not know that 03/01/2001 and 04/01/2001 are supposed to be dates, and is probably interpreting them as division equations. Use this syntax instead:
    O.ORDER_DATE >= '03/01/2001' and O.ORDER_DATE < '04/01/2001'
    SQL Server will implicitly translate the date strings into datetime data types.

    blindman

  6. #6
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    1 . Dates need to be enclosed in a ' '
    2 . convert(varchar(10),O.ORDER_DATE,101) brings the datetime field into the same format you are trying to compare with i.e mm/dd/yyyy
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ..but Enigma's CONVERT function is only necessary if your data is not already stored in a datetime column (it should be!).

    blindman

  8. #8
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Blindman ...
    in case the frontend entry is using getdate() to insert into sql server ... using convert makes pretty good sense ...
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It's not necessary if the data is being stored in datetime format. The table doesn't know or care about the front-end. If the data is being stored as a string, then then convert is probably a good idea.

    blindman

  10. #10
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    use northwind
    go
    create table abc
    (
    name varchar(256)
    , refdate datetime
    )

    insert into abc values ('sysjobschedules','2000-08-08 01:33:14.763')
    insert into abc values ('RTblIfaceMem','2000-08-12 01:43:44.547')
    insert into abc values ('backupfile','2000-08-07 01:33:37.140')
    insert into abc values ('syscategories','2000-08-06 01:33:14.890')
    insert into abc values ('systargetservers','2000-08-06 01:33:15.250')
    insert into abc values ('RTblWorkspaceItems','2000-08-07 01:43:44.560')
    insert into abc values ('restorehistory','2000-08-06 01:33:37.327')
    insert into abc values ('systargetservergroups','2000-08-07 01:33:15.500')
    insert into abc values ('RTblDatabaseVersion','2000-08-12 01:43:44.577')
    insert into abc values ('systargetservergroupmembers','2000-08-10 01:33:15.623')
    insert into abc values ('sysalerts','2000-08-09 01:33:15.750')

    go

    now :
    Q. How would you select rows where refdate is '06/08/2000'
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  11. #11
    Join Date
    Oct 2003
    Posts
    6
    Well either way works fine for this situation, thanks tons for all the help guys!

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Ummm....

    Select * from abc where refdate is '06/08/2000'????

    convert(varchar(10),O.ORDER_DATE,101) between '03/01/2001' and '03/31/2001'
    ...is equivalent to:
    O.ORDER_DATE between '03/01/2001' and '03/31/2001'
    ..when the data is in the whole-date values that Osiris specified. Otherwise:
    O.ORDER_DATE >= '03/01/2001' < '04/01/2001'
    ...also does the job.

    When you wrote: "convert(varchar(10),O.ORDER_DATE,101) brings the datetime field into the same format you are trying to compare with i.e mm/dd/yyyy" it sounds as if you are saying that a value stored as yyyy/mm/dd must be converted before it can be compared to mm/dd/yyyy. I think what you meant is that any time portion needs to be truncated or a value such as '03/31/2001 08:15:32' would be excluded.

    I just wanted to make sure that Osiris1012 understands that the format in which a datetime field is displayed has nothing to do with how it is stored or how it was entered. ie that it is acceptable to compare the value 6/9/2000 to either '06/09/2000' or '2000-06-09' or '2000/06/09'.

    blindman

  13. #13
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    That was exactly what I was trying to say ...
    ... sorry if it was not that clear.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

Posting Permissions

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