Quick SQL Server tip

[baavgai]

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.

Comments

[mare-in-flames.livejournal.com]

*shrug* exactly what you have there, that's as well as I ever do with breaking up DateTime... but I'm not all that and a bag of chips when it comes to the code. Mostly I just whine at my database until it tells me what I want to know...

Maybe someone else will actually say something intelligent.

[maccuswell.livejournal.com]

Date handling has got to be one of the most annoying aspects of computing, like, ever. As evidence, I point out that every damn application out there does it differently.

I don't do SQL Server, unfortunately, so I don't have anything better.