Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    20

    Unanswered: TO compare date in a SQL query - SQL Syntax question?

    In my SQL query I am trying to retreive records from one of my column 'ReceiptDt' that is all the records received after 28.
    How do I need to incorporate in my SQL query.
    Select * from table A
    WHERE DATEPART(DAY, ReceiptDt) >= '28'
    The error msg I get is :
    Server: Msg 170, Level 15, State 1
    Incorrect syntax near 'DATEPART'.

    Any ideas??

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    DATEPART(dd,ReceiptDt) >= '28'


    Realize though, that's any month...so all days in any month that are greater than 28 for any year, qualify...
    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.

  3. #3
    Join Date
    Sep 2003
    Posts
    522
    have you tried to click anywhere on qa window to cancel the highlight? i managed to replicate your problem by highlighting "Select * from table A WHERE DATEPART".

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149

    Smile Re: TO compare date in a SQL query - SQL Syntax question?

    I dont get any errors !!!!!!!!!
    Originally posted by sri2003
    In my SQL query I am trying to retreive records from one of my column 'ReceiptDt' that is all the records received after 28.
    How do I need to incorporate in my SQL query.
    Select * from table A
    WHERE DATEPART(DAY, ReceiptDt) >= '28'
    The error msg I get is :
    Server: Msg 170, Level 15, State 1
    Incorrect syntax near 'DATEPART'.

    Any ideas??

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: TO compare date in a SQL query - SQL Syntax question?

    Originally posted by sbaru
    I dont get any errors !!!!!!!!!
    True enough

    SELECT DATEPART(DAY,Getdate())


    But it's still any 29th, 30th, or 31st day, for any month, for any year...

    EDIT: But that can't be the actual SQL...

    FROM Table A?
    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.

  6. #6
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    20

    Re: TO compare date in a SQL query - SQL Syntax question?

    Thanks a lot for your quick replies.....I made a simple mistake in my query in my WHERE clause by not adding AND before this DATEPART..doh!
    Thanks

    Originally posted by sbaru
    I dont get any errors !!!!!!!!!

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I hate when that happend

    pulled a homer....
    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.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    DATEPART returns an integer, so you should be comparing it to an integer instead of a string. Your statement should read:

    WHERE DATEPART(DAY, ReceiptDt) >= 28

    blindman

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    [doooh]
    Good point
    [/doooh]

    How did I miss that one...

    Still...the whole thing doesn't make sense to me....
    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.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149
    Do you think this matters? Will there not be an implicit conversion that happens before the comparison? Just curious.


    Originally posted by blindman
    DATEPART returns an integer, so you should be comparing it to an integer instead of a string. Your statement should read:

    WHERE DATEPART(DAY, ReceiptDt) >= 28

    blindman

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, SQL server will implicitly convert the character string to the numeric value, so this is not the cause of the error he was receiving. It was just a minor programming point I wanted to make him aware of.

    blindman

  12. #12
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    20
    I am still having issues with this query. I wanted to retrieve all the records that where received after 25 of October till today.
    Part of my query where I am not able to acheive this is:
    <Q 1>
    DATEPART(month, s.ReceiptDt) = 10
    AND DATEPART(YEAR, s.ReceiptDt) = 2003
    AND DATEPART(dd, s.ReceiptDt) >= 25
    This retreives all the records of October 25 but not Nov - till date.

    Any ideas??

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by blindman
    Yes, SQL server will implicitly convert the character string to the numeric value, so this is not the cause of the error he was receiving. It was just a minor programming point I wanted to make him aware of.

    blindman
    There are no minor points...

    And now you've realized that you want to worry about the month...

    When are you going to realize you need to worry about the year?

    Tell us what you need to do, from a business perspective....

    Foget the technical....that's the easy part...


    "I've got an elephant, but it won't fit in my car...."
    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.

  14. #14
    Join Date
    Oct 2003
    Location
    Ohio
    Posts
    20
    Yes...I think you nailed it correctly. I did not relaize about the year yet but I should have. I need to pull the records from the table since the report was last generated. That is If I generate report on Oct 25 and I want to pull records today - I have to acount for records from Oct 26 till Nov 17. Does this make sense?
    Thanks

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149
    Sri2003,

    You are confusing us again. If you have a date and you want to retrieve all the records after that date, why split the date into parts? Just use

    WHERE s.ReceiptDate > @LastReportGeneratedDate

    The query you wrote will retrive records from Oct 26th to Oct 31 st.

    - Sarat
    Originally posted by sri2003
    Yes...I think you nailed it correctly. I did not relaize about the year yet but I should have. I need to pull the records from the table since the report was last generated. That is If I generate report on Oct 25 and I want to pull records today - I have to acount for records from Oct 26 till Nov 17. Does this make sense?
    Thanks

Posting Permissions

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