CODEWITHSUNDEEP
sql-serveroracle-database
Mike Haff
Mike Haff

Reputation: 3

CONVERTING TO_DATE FROM ORACLE TO SQL SERVER

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

Upvotes: 0

Views: 4185

Answers (3)

hilley
hilley

Reputation: 21

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

Upvotes: 0

Dave C
Dave C

Reputation: 7392

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

Upvotes: 0

Code Different
Code Different

Reputation: 93151

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

Upvotes: 1

Related Questions