Interesting SQL Facts
Yesterday I found out there's a DateDiff function in SQL Server that works the same as ColdFusion's DateDiff. Today I was curious if DateDiff is part of the ANSI SQL standard, or if it's a proprietary Microsoft SQL extension. I never found my answer, but it doesn't look like Oracle supports it. However, I did find some interesting facts about SQL:
- SQL is officially pronounced "es queue el" rather than "sequel". This is part of the ANSI standard, but English speakers usually use the non-standard pronunciation.
- The ANSI standard is NOT free. You have to buy it, which I think is very stupid. Standards should always be free, in my opinion.
- SQL was originally invented by IBM in the 70's, and was called "Structured English Query Language". Of course, today "English" has been dropped from the acronym.
Jake Munson
37 Yrs old
Sybase users are used to using datediff in the database and are many times baffled by the lack of a function to do date arithemetic that they assume Oracle cannot do it. It is really just that date arithmetic is so trivial that a specialized function like datediff is not needed. Just subtract. You get the difference in days. Multiply by 24 -- hours, multiply by 60 minutes, multiply by 60 -- seconds.
I'm assuming that the link you posted is to a draft RFC version of the ANSI standard. I suppose that's better than nothing, but they should still make the official version free.
Coldfusion returns the date in 24 hours whereas MSSQL (not sure about other DBs) returns the actual different is dayes.(ie in coldfusion the dateDiff for days between 22:00 11/09/2007 and 10:00 12/09/2007 is 0 days (the hours different is less that 24 hours) whereas in MSSQL is 1 day (MSSQL subtracts the date values not caring about the hours)). The same goes for months, weeks years etc...
I was using the dateDiff function in MSSQL to find the difference in minutes, which seems to work the same way that it does in ColdFusion.
In sql
select dateDiff(minute,'22:00:10','22:10:00')
result: 10
in coldfusion
cfset t1 = createTimeSpan(0,22,0,10)
cfset t2 = createTimeSpan(0,22,10,0)
CFSET dif = DateDiff('n',t1,t2)
Result is: 9