JustDate: Striping the time part from datetime variables
time to read 5 min | 867 words

In oracle, there is a handy little function call trunc. That function is a real work hourse, it can truncate numbers, dates, programmers life time, etc. In the most basic form trunc(sysdate) will return the current day without the time part, it has quite a few options that are fairly nice as well. Anyone, for some reason this bulitin functionality is missing in T-SQL, so of course I had to implement it three times. The first was a scratch function that used string manipulation to do its work. The second was a SQL CLR function that just did:

[SqlFunction]
public DateTime JustDate(DateTime dateTime)
{
    return dateTime.Date;
}

This has so many problems that I won't begin to count them just yet. Let's just say that I finally realized how bad this is when I started to get errors about "can't accept null values". And then came enlightment, and I wrote this function:

CREATE FUNCTION JustDate(@date DATETIME)

RETURNS DATETIME

AS

BEGIN

      RETURN DATEADD(day,day(@date)-1,   

                  DATEADD(month,month(@date)-1,

                        DATEADD(year,year(@date)-1900,0)))

      RETURN DATEADD(dd, 0, DATEDIFF(dd, 0, @date))

END

It uses the same technique as the previous one.

I think that this one is much better :-D

Update: Anatoly has a much simpler version, I updated the post to show the new method (the old method is striked through.

Tweet Share Share 0 comments
Tags: