Date from datetime ...

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
ChristianNone of the alternatives you posted are good alternatives.
I've elaborated on the topic in http://www.karaszi.com/SQLServer/in...me.asp
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Christian Donner" <ChristianDonner@.discussions.microsoft.com> wrote in mess
age
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 seco
nd
> 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|||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
"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 seco
nd
> 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|||"Tibor Karaszi" wrote:
> None of the alternatives you posted are good alternatives.
> I've elaborated on the topic in [url]http://www.karaszi.com/SQLServer/info_datetime.asp.[/url
]
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!|||"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!|||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) techni
que.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Christian Donner" <ChristianDonner@.discussions.microsoft.com> wrote in mess
age
news:FBCBC7B9-CA55-4851-9C95-6EF472ABA8F7@.microsoft.com...
> "Tibor Karaszi" wrote:
> That's not exactely what I was searching for, but it contains very valuabl
e
> hints for the solution (the result is somewhat serendipitious ;-). Thank y
ou
> very much for your help!|||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
****************************************
************************|||"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.