CONVERTING TO_DATE FROM ORACLE TO SQL SERVER

Question

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')

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. — user330315
– user330315, Commented Oct 3, 2014 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
– jpmc26, Commented Oct 3, 2014 at 16:17
@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. — user330315
– user330315, Commented Oct 3, 2014 at 16:36

Code Different · Accepted Answer · 2014-10-03 15:12:42Z

1

CONVERT(datetime,TBL_TMX_ATTEMPT.LSTUPD) > DATEADD(DAY,-5,GETDATE())

answered Oct 3, 2014 at 15:12

Code Different

93.4k16 gold badges154 silver badges175 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dave C · Accepted Answer · 2014-10-03 15:14:11Z

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

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
I would have upvoted if not for using string manipulation to remove the time. Other approaches can be found here: stackoverflow.com/questions/1177449/….

hilley · Accepted Answer · 2014-10-03 15:23:21Z

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 ] )

Collectives™ on Stack Overflow

CONVERTING TO_DATE FROM ORACLE TO SQL SERVER

3 Answers 3

Comments

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Linked

Related