SQL 92? Date Problem

[mlocurci]

I am trying to hook my SQL DB to a report engine that says it supports SQL 92. I dont really know what that means, except that it cant handle this part of my SQL statement.

Can someone tell me how I can accomplish the same thing as this using sql 92 standards? Basically I need to get all the date for 'Yesterday'

(MESSAGESENDERCATEGORYVIEW.CompleteDATE >= DateAdd(Day,DateDiff(Day,0,GetDate()-1),0) AND MESSAGESENDERCATEGORYVIEW.CompleteDATE<DateAdd(Day,DateDiff(Day,0,GetDate()),0))

 

[r937]

SQL 92 refers to a level of Standard SQL

unfortunately, Standard SQL does not include microsoft date functions such as DateAdd, DateDiff, or GetDate

however, your report engine might support them

how can you find out?





r937.com | rudy.ca

 

[Qik3Coder]

There should be another way to get at the date from inside your reporting engine.

Btw, it looks like you're date result isn't what you might want. you have weird issues when you start parting out the date into bits.

I've always found that this works pretty well, when i need a date

--Showed work...
Select getDate(), getDate()-1, left(getDate()-1, 11), cast(left(getDate()-1, 11) as datetime)

-Sometimes the answer to your question is the hack that works

 

[lameid]

Another thought...

You could make a view that runs the desired SQL statement.

Then use the view as if it is a table in your reporting product. I doubt it would know the difference between a view and a table as the below statement is ANSI-92 SQL compliant syntax, although it says nothing about views itself.

Select ViewName.* 
From ViewName

BTW the 92 comes from 1992. It's precursor was the ANSI-89 SQL standard.