Scroll Cursor and variables

  • Folks

    When I retrieve a value from an access database (using openquery) using a scroll cursor on a date field the value returns correctly.  When I then assign this value to a variable using 

    fetch last from time_cursor into @var 

    the value seems to change format regardless of whether the variable type is varchar or datetime.

    e.g the result from the scroll cursor is 2005-02-28 10:27:22.000 but becomes Feb 28 2005 10:27AM when assigned to a variable.

    I want to be able to search another linked sqlserver db using the datetime value as the search criteria.

    Any ideas?

  • remember a datetime is actually stored as a decimal type number, and the format is arbitrary....its stored as a number like 12345.67, where the 12345 is whole days, and the .67 is the fraction of a day.

    your scroll cursor may display by default as one format, while your connection may have a default display of another format.

    you can compare datetimes as needed; you can do scroll_cursor > '01/15/2005' or scroll_cursor > '2005-02-28 10:27:22.000', and see how the comparison operator tries to implicily convert it to a date and do the compare.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply