Scroll Cursor and variables

• Axel

  SSC Rookie

  Points: 39

  More actions

  May 29, 2005 at 1:26 pm

  #164503

  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?

• Lowell

  SSC Guru

  Points: 323524

  More actions

  May 29, 2005 at 8:12 pm

  #561637

  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!