baavgai: (Default)
( Jan. 3rd, 2007 12:23 pm)
Most systems reckon time as a function of the date and the time. All databases I know of do this. Even those that say they have a different type for date and time just usually just hide the other half.

Oracle's method of storage is kind of neat. It's a big number that's the number of days since the year 1700. The time is a fraction. So, to get just a date, Oracle has this:

select trunc(sysdate) from dual

If you don't know this, there is an ugly hack that takes advantage of character conversions to cut off the time, it looks like this:

select to_date(to_char(sysdate,'YYYYMMDD'),'YYYYMMDD') from dual

Micrsofts SQL Server doubtless has a similar mechanism somewhere. However, the equivalent of Oracle's trunc(sysdate) doesn't seem available. There are many methods out there, all kind of cludgy. That character conversion hack for Oracle I noted? Well, that kind of method is the best I've found for SQL Server. And here is is:

SELECT CONVERT(datetime, CONVERT(varchar(10), GetDate(), 101))

Please, if anyone finds a better one, let me know.


baavgai: (Default)



RSS Atom

Most Popular Tags

Page Summary

Powered by Dreamwidth Studios

Style Credit

Expand Cut Tags

No cut tags