Date from datetime ...

2 Dec 2005 8:21 AM

Christian Donner

Hello NG,

we all know the problem of (date) equality checks on datetime fields
preventing us from issuing a command like:

   SELECT * FROM [MyTable]
      WHERE [SomeDateField] = @SomeDateVariable

As datetime contains time information down to the milliseconds this query
will usually return nothing. The alternatives

   SELECT * FROM [MyTable]
      WHERE YEAR([SomeDateField]) = YEAR(@SomeDateVariable)
      AND MONTH([SomeDateField]) = MONTH(@SomeDateVariable)
      AND DAY([SomeDateField]) = DAY(@SomeDateVariable)

   SELECT * FROM [MyTable]
      WHERE [SomeDateField] BETWEEN '20050510 000001' AND '20050510 235959'

are not really comfortable (the first one contains too much code, the second
one is not possible if you received the variable's content from somewhere
else). Has anyone come across a function that works like good ol' VB's
Date(), which delivers only the (complete) datepart (without the time part)?
Any hint would be greately appreciated.

Thanks in advance
Christian

2 Dec 2005 8:32 AM

Tibor Karaszi

None of the alternatives you posted are good alternatives.
I've elaborated on the topic in http://www.karaszi.com/SQLServer/info_datetime.asp.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quote

"Christian Donner" <ChristianDon***@discussions.microsoft.com> wrote in message
news:609BD29D-3935-4099-BC64-0F14ABAD7237@microsoft.com...
> Hello NG,
>
> we all know the problem of (date) equality checks on datetime fields
> preventing us from issuing a command like:
>
>   SELECT * FROM [MyTable]
>      WHERE [SomeDateField] = @SomeDateVariable
>
> As datetime contains time information down to the milliseconds this query
> will usually return nothing. The alternatives
>
>   SELECT * FROM [MyTable]
>      WHERE YEAR([SomeDateField]) = YEAR(@SomeDateVariable)
>      AND MONTH([SomeDateField]) = MONTH(@SomeDateVariable)
>      AND DAY([SomeDateField]) = DAY(@SomeDateVariable)
>
>   SELECT * FROM [MyTable]
>      WHERE [SomeDateField] BETWEEN '20050510 000001' AND '20050510 235959'
>
> are not really comfortable (the first one contains too much code, the second
> one is not possible if you received the variable's content from somewhere
> else). Has anyone come across a function that works like good ol' VB's
> Date(), which delivers only the (complete) datepart (without the time part)?
> Any hint would be greately appreciated.
>
> Thanks in advance
> Christian

2 Dec 2005 9:45 AM

Christian Donner

"Tibor Karaszi" wrote:
> None of the alternatives you posted are good alternatives.
> I've elaborated on the topic in http://www.karaszi.com/SQLServer/info_datetime.asp.

That's not exactely what I was searching for, but it contains very valuable
hints for the solution (the result is somewhat serendipitious ;-). Thank you
very much for your help!

2 Dec 2005 9:57 AM

Tibor Karaszi

I'm glad you found it helpful (and meanwhile I learned a new English word ;-) ).

Now that you read (parts of) the article, I take it that the rest is rather simple. Convert to a
character string using code 112. Then use the > AND <= (plus one day) technique.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/

Show quote

"Christian Donner" <ChristianDon***@discussions.microsoft.com> wrote in message
news:FBCBC7B9-CA55-4851-9C95-6EF472ABA8F7@microsoft.com...
> "Tibor Karaszi" wrote:
>> None of the alternatives you posted are good alternatives.
>> I've elaborated on the topic in http://www.karaszi.com/SQLServer/info_datetime.asp.
>
> That's not exactely what I was searching for, but it contains very valuable
> hints for the solution (the result is somewhat serendipitious ;-). Thank you
> very much for your help!

2 Dec 2005 9:45 AM

Damien

This is my favorite method for stripping time from date:

-- Strip time from today's date
SELECT DATEADD( day, DATEDIFF( day, 0, GETDATE() ), 0 )

Where GETDATE() can be substituted for your date. Credit to Steve Kass.

Damien

Show quote

"Christian Donner" wrote:

> Hello NG,
>
> we all know the problem of (date) equality checks on datetime fields
> preventing us from issuing a command like:
>
>    SELECT * FROM [MyTable]
>       WHERE [SomeDateField] = @SomeDateVariable
>
> As datetime contains time information down to the milliseconds this query
> will usually return nothing. The alternatives
>
>    SELECT * FROM [MyTable]
>       WHERE YEAR([SomeDateField]) = YEAR(@SomeDateVariable)
>       AND MONTH([SomeDateField]) = MONTH(@SomeDateVariable)
>       AND DAY([SomeDateField]) = DAY(@SomeDateVariable)
>
>    SELECT * FROM [MyTable]
>       WHERE [SomeDateField] BETWEEN '20050510 000001' AND '20050510 235959'
>
> are not really comfortable (the first one contains too much code, the second
> one is not possible if you received the variable's content from somewhere
> else). Has anyone come across a function that works like good ol' VB's
> Date(), which delivers only the (complete) datepart (without the time part)?
> Any hint would be greately appreciated.
>
> Thanks in advance
> Christian

2 Dec 2005 9:51 AM

Christian Donner

"Damien" wrote:
> This is my favorite method for stripping time from date:
> -- Strip time from today's date
> SELECT DATEADD( day, DATEDIFF( day, 0, GETDATE() ), 0 )
>
> Where GETDATE() can be substituted for your date. Credit to Steve Kass.
>
> Damien

Nice idea. Thanks!

2 Dec 2005 10:17 AM

Tapio Kulmala

CREATE function dbo.TimeStrip (@dt datetime)
returns datetime
AS
begin
return convert(datetime, convert(varchar(10), @dt, 102), 102)
end

GO

select dbo.TimeStrip ( getdate())

select dbo.TimeStrip ( null )

--
****************************************************************
Tapio Kulmala

"Those are my principles. If you don't like them I have others."

- Groucho Marx
****************************************************************

2 Dec 2005 1:27 PM

Christian Donner

Show quote

"Tapio Kulmala" wrote:

> CREATE function dbo.TimeStrip (@dt datetime)
> returns datetime
> AS
> begin
> return convert(datetime, convert(varchar(10), @dt, 102), 102)
> end
>
> GO
>
> select dbo.TimeStrip ( getdate())
>
> select dbo.TimeStrip ( null )
>
> Tapio Kulmala

This way I loose access to the index (as Tibor pointed out in his excellent
article). Thank you for the suggestion.