Pages

Tuesday, November 09, 2004

SQLServer: DataWarehouses and DATETIME

So we want to grab significant transactions from yesterday for our datawarehouse, but we're not quite sure exactly what time our DTS extraction jobs will run.

What is the easiest way to get all rows that have a datetime column entry from yesterday? With Oracle, we just use trunc sysdate - 1 and trunc sysdate, but I haven't been able to find a good trunc function in SQLServer.

Here's what we've come up with... (Ugly, but effective)



SELECT *
FROM audit
WHERE moddate >= CAST(CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR)
+ '-' + CAST(CAST(DATEPART(MONTH,GETDATE()) AS VARCHAR)
+ '-' + CAST(CAST(DATEPART(DAY,GETDATE()) AS VARCHAR) AS DATETIME) - 1
AND moddate <= CAST(CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR) + '-' + CAST(CAST(DATEPART(MONTH,GETDATE()) AS VARCHAR) + '-' + CAST(CAST(DATEPART(DAY,GETDATE()) AS VARCHAR) AS DATETIME)



[ audit table has various fields, with moddate as datetime datatype. ]




Post a Comment