0

I am migrating my oracle database to SQL Server. I'm in need of some help converting this one line of code in my WHERE clause

TO_DATE(TBL_TMX_ATTEMPT.LSTUPD) > (SYSDATE - '5')
Maheswaran Ravisankar
  • 17,652
  • 6
  • 47
  • 69
Mike Haff
  • 3
  • 1
  • 2
  • `SYSDATE - '5'` is not a good style in the first place. `'5'` is a character literal, not a number. You should always use the correct literals. `5` is a number. Btw: Why are you storing a date in a `varchar` column? If you are migrating you should also take the chance to use the correct datatype and store that in a `date` or `datetime` column. Storing a date in a character column is a really bad choice. –  Oct 03 '14 at 15:17
  • @a_horse_with_no_name I should think "date - 5" is bad form, anyway. You're subtracting 5 what? Days? Months? Years? I guess if you've been working with Oracle a long time, you would probably know, but not everyone does. Better to make it clear. On a side note, when I see `LSTUPD`, I can't help but think it's supposed to read, "L-stupid." – jpmc26 Oct 03 '14 at 16:17
  • 1
    @jpmc26: the meaning of `date - 5` is well defined and documented in Oracle (it's days). It's the same in other DBMS (Postgres, Firebird, H2 also work that way). But I agree: using a standard interval `date - interval '5' day` is probably clearer and more portable. –  Oct 03 '14 at 16:36

3 Answers3

1
CONVERT(datetime,TBL_TMX_ATTEMPT.LSTUPD) > DATEADD(DAY,-5,GETDATE())
Code Different
  • 90,614
  • 16
  • 144
  • 163
0

You can do:

WHERE CONVERT(DATETIME,TBL_TMX_ATTEMPT.LSTUPD) > GETDATE()-5

If LSTUPD is already in a datetime, then omit the CONVERT(). No need to run the conversion if it is already IN the right format.

Also keep in mind GETDATE() includes a time stamp. So this is the current date/time - 5 days.

If you want to get 5 days before midnight use this:

WHERE CONVERT(DATETIME,TBL_TMX_ATTEMPT.LSTUPD) > CONVERT(DATETIME,CONVERT(VARCHAR(10),GETDATE(),120))-5

Dave C
  • 7,272
  • 1
  • 19
  • 30
  • Thanks JiggsJedi, I used the first one but without the convert and it worked great. I forgot that the Oracle code needed to convert the date to a string eventually. I do not need to do that in my sql version, just running the query – Mike Haff Oct 03 '14 at 15:35
  • I would have upvoted if not for using string manipulation to remove the time. Other approaches can be found here: http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server. – jpmc26 Oct 03 '14 at 16:23
0

It's important to know what the data type of TBL_TMX_ATTEMPT.LSTUPD is. If it is a VARCHAR2 or other string type (BAD choice for storing dates, btw), you need to take the date formats into consideration when calling CONVERT in SQL Server. Look up the Date/Time Style parameter of the CONVERT function for more info.

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
hilley
  • 21
  • 3