DateTime acting strange

[howardd21]

Here is one that is odd to me, although I am sometimes a bit nuts

I have a table with a DateTime column I am querying on. IF I run this query:
Select * From CTKeyWords_Del Where Date_Deleted < '1997/07/16'
I get 5 records back for July 15, 1997.

But if I run this query:
Select * From CTKeyWords_Del Where Date_Deleted = '1997/07/15'

I get back no records. This table seems to have a problem with any query on this field and the &quot;=&quot; operator.

Any ideas?

David

 

[JJayUK]

SQL Server sometimes considers all dates to include the time.

If no time is supplied (just the date as above) SQL Server will set it to Midnight (00:00:00). So you're telling SQL Server to look for '1997/07/15 00:00:00' (exactly midnight on 15th July).

Using the '<' though your telling SQL Server to look for any date LESS than '1997/07/15 00:00:00' which is why you're getting back data for the 15th.

Hope this helps
JJayUK

 

[howardd21]

Thanks for the quick response! I see where it is dong this, and if I run this query:

Select * From CTKeyWords_Del Where Date_Deleted Between '1997/07/15' + ' 00:00:00' AND '1997/07/15' + ' 23:59:59'

I get back the 5 rows I expected.

But I need to pass a date to a Stored proc, and I am having trouble getting it to work in there. Here is what I am trying in the SP:
======================
create proc uspRestoreKeyWords (
@UserID varchar(10),
@DateDeleted DateTime) as

Declare @StartDate DateTime
Declare @EndDate DateTime

Select @StartDate = @DateDeleted + ' 00:00:00'
Select @EndDate = @DateDeleted + ' 23:59:00'

Select ContactID, SubKeywordID, KeyWord, Status
From CTKeyWords_Del
Where Date_Deleted Between @StartDate AND @EndDate

GO
==================
But I get an error saying:
Invalid operator for datatype op: add type: datetime

I have tried some Convert() functions, but they do not seem to work.

David

 

[howardd21]

Hey! I got it. I was being obtuse. I ended up with:
=======================
create proc uspRestoreKeyWords (
@UserID varchar(10),
@DateDeleted VarChar(20)) as

Declare @StartDate VarChar(40)
Declare @EndDate VarChar(40)

Select @StartDate = @DateDeleted + ' 00:00:00'
Select @EndDate = @DateDeleted + ' 23:59:00'

Insert Into CTKeyWords (ContactID, SubKeywordID, KeyWord, Status)
Select ContactID, SubKeywordID, KeyWord, Status
From CTKeyWords_Del
Where Deleted_By = @UserID
AND Date_Deleted Between @StartDate AND @EndDate
GO
======================

This works fine, since I keep everything as a Char until the actual query converts it (in the end it needed to be an Insert command).

Thanks!

 

[RickCole]

Don’t worry about the time just go to the next day in your comparison i.e. in your case the 16th of July.

Using 'between' SQL Server will automatically pick up dates less then the 16th but not including

Select * From CTKeyWords_Del Where Date_Deleted Between '1997/07/15' AND '1997/07/16'

This also equates to

Select * From CTKeyWords_Del Where Date_Deleted >= '1997/07/15' AND Date_Deleted < '1997/07/16'

Rick.

 

[howardd21]

Thanks. That is helpful to know, and I imagine faster and more maintainable.

David

 

[arrowhouse]

In your top sql the '1997/07/15' + ' 00:00:00' is probably being considered as a char (so the concatenation is ok) & then is implicitly translated to a datetime.

In the sp you have declared the variables as datetimes so concatenation is not ok. You can use the dateadd & datediff functions to add date elements (including times) to your variables. Or you could pass them as varchars concatenate & then convert to dates!