CODEWITHSUNDEEP
sqlsap-asegetdate
plankton
plankton

Reputation: 325

Type Clash when using Datediff(...Dateadd(...),Getdate()) in Sybase

I've switched companies and in doing so, switched from SQL Server to Sybase-ASE. I can't tell if I'm just having a brain fart and am coding incorrectly or if there is a difference between running Datediff(...Dateadd(...),Getdate()) in SQL Server and Sybase-ASE. I'm getting a type clash (INT) on the Datediff side of the formula when running the code below. Running Dateadd by itself works fine. Not sure if something needs to be tweaked or it just can't be done in Sybase-ASE. Any help? I checked these other questions but no real help (Subtract one day from datetime GETDATE last month Get the records of last month in SQL server Datediff GETDATE Add)

SELECT TOP 5 *

FROM SalesData fm
WHERE fm.Date = DATEDIFF(MONTH, DATEADD(MONTH,-1,MAX(fm.Date)),GETDATE())

ALSO, side question: I can't remember what the specific formula is to get the whole of last month is in Datediff if anyone happens to know. I can figure it out I just figured it's best to kill two birds with one stone. It's apparently somewhat difficult to get a Dates table implemented into production...

Upvotes: 0

Views: 685

Answers (2)

RobV
RobV

Reputation: 2378

On the point of built-in functions, ASE and SQLServer are nearly identical. There is however one difference, and this is what you may be hitting. In SQLServer, you can use a numeric value for a date, which is interpreted in SQLServer as #days since 01-01-1900. ASE does not support using numeric values as a date, and you will get a syntax error.

Upvotes: 1

plankton
plankton

Reputation: 325

Nevermind, I got it thanks to this post(Get the records of last month in SQL server)

where DATEPART(MONTH,fm.Date) = DATEPART(MONTH, DATEADD(MONTH,-1,GETDATE()))
AND DATEPART(YEAR,fm.Date) = DATEPART(YEAR, DATEADD(YEAR,0,GETDATE()))

Upvotes: 0

Related Questions