Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Date

  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Date

    does anyone see anything wrong with this statement, sql keeps telling me "'Date' is not a recognized function name."

    Code:
    INSERT INTO [Exlookup-Changes] ( [Last Name], [First Name], MI, Sex, DOB, [Date], [Action taken] )
    SELECT [30 day exclusion].[Last Name], [30 day exclusion].[First Name], [30 day exclusion].MI, [30 day exclusion].Sex, [30 day exclusion].DOB, [30 day exclusion].timestamp, "ADDED" AS Expr1
    FROM [30 day exclusion]
    WHERE ((([30 day exclusion].timestamp) Between Date()-2 And Date()+2));

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Try:
    Between GetDate()-2 And GetDate()+2

    Regards,

    hmscott


    Quote Originally Posted by desireemm
    does anyone see anything wrong with this statement, sql keeps telling me "'Date' is not a recognized function name."

    Code:
    INSERT INTO [Exlookup-Changes] ( [Last Name], [First Name], MI, Sex, DOB, [Date], [Action taken] )
    SELECT [30 day exclusion].[Last Name], [30 day exclusion].[First Name], [30 day exclusion].MI, [30 day exclusion].Sex, [30 day exclusion].DOB, [30 day exclusion].timestamp, "ADDED" AS Expr1
    FROM [30 day exclusion]
    WHERE ((([30 day exclusion].timestamp) Between Date()-2 And Date()+2));
    Have you hugged your backup today?

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I would use:
    Code:
    INSERT INTO [Exlookup-Changes] (
       [Last Name], [First Name], MI
    ,  Sex, DOB, [Date], [Action taken]
    )  SELECT
       a.[Last Name], a.[First Name], a.MI
    ,  a.Sex, a.DOB, a.[timestamp], "ADDED" AS Expr1
       FROM [30 day exclusion] AS a
    WHERE a.[timestamp] BETWEEN DateAdd(day, -2, GetDate())
       AND DateAdd(day, 2, GetDate())
    SQL Server uses GetDate() while Jet uses Date() to get the current date. While it works to add an integer to a date to add days, I really, REALLY don't like it... I think that is a bad habit to get into, because different products implement dates differently, and this is just an accident waiting to happen.

    I substituted an alias of a for the [30 day exclusion] formal qualifier in the SELECT list. That just makes things easier for me to read/understand.

    If you have the option to change the name of the timestamp column, I would STRONGLY suggest that you change it. timestamp is a reserved word and a datatype in SQL Server. That would be like having a column named CHAR in my opinion, it can be made to work, but why set yourself up for grief?

    -PatP

  4. #4
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    error

    the problem I'm having now is an error message that says "Invalid Column Name Added"

    Code:
    INSERT INTO [Exlookup-Changes] ( [Last Name], [First Name], MI, Sex, DOB, [Date], [Action taken] )
    SELECT [30 day exclusion].[Last Name], [30 day exclusion].[First Name], [30 day exclusion].MI, [30 day exclusion].Sex, [30 day exclusion].DOB, [30 day exclusion].timestamp, "ADDED" AS Expr1
    FROM [30 day exclusion]
    WHERE ((([30 day exclusion].timestamp) Between GetDate()-2 And GetDate()+2));

  5. #5
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    stored procedure

    can I make that into a stored procedure and use the DECLARE STATEMENT
    DECLARE
    {{ @local_variable data_type }

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Try using [timestamp] instead of using it "bare" (without the brackets). If that fixes the problem, it is what I was referring to in my earlier post in this message thread.

    -PatP

  7. #7
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    0 rows affected

    well I tried doing it this way, and it tells me 0 rows affected. Pat can you explain what you mean please.
    are you saying take the brackets off timestamp instead of it being [timestamp]
    (a.[Last Name], a.[First Name], a.MI
    , a.Sex, a.DOB, a.[timestamp], "ADDED" AS Expr1
    FROM [30 day exclusion] AS a
    WHERE a.[timestamp] BETWEEN DateAdd(day, -2, GetDate())
    AND DateAdd(day, 2, GetDate()))

    Code:
    INSERT INTO [Exlookup-Changes] ( [Last Name], [First Name], MI, Sex, DOB, [Date], [Action taken] )
    SELECT [30 day exclusion].[Last Name], [30 day exclusion].[First Name], [30 day exclusion].MI, [30 day exclusion].Sex, [30 day exclusion].DOB, [30 day exclusion].timestamp, 'ADDED' AS Expr1
    FROM [30 day exclusion]
    WHERE ((([30 day exclusion].timestamp) Between GetDate()-2 And GetDate()+2));
    Result 0 Rows affected

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Check out what BOL has to say about timestamp first. Because it is a datatype in SQL Server, and a really unusual one at that, it gets treated differently by SQL itself as well as many tools. Because of that, I would avoid having a column named timestamp like the plague, unless it was really a timestamp.

    The work around is to always, always, always use brackets around the timestamp column. Those will force the well behaved applications to treat it as a column name, not as a semi-magical reference.

    -PatP

  9. #9
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Thank you

    Thanks Pat for helping...and Um one other thing are you feeling ok???

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by desireemm
    Thanks Pat for helping...and Um one other thing are you feeling ok???
    Who is Ok?

    -PatP

  11. #11
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Wink or

    Whats Ok??? Ya know as in whats qualified as normal...depending on your Interpretation

  12. #12
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Ummm

    what I meant was are you feeling alright?? umm Nevermind

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sorry, that's a long, slippery slope!

    -PatP

  14. #14
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Oh Ok

    Ok Pat thanks for your help

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just to kind of drag this thread back onto topic, did my comments about the use of timestamp help clear things up, or did they just further muddy the water?

    -PatP

Posting Permissions

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