Filter Datetime - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Filter Datetime

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-01-23 : 13:00:50

I have a sql statement that using applydate as the filter.
Here is the table

  
 [ID]    [ApplyDate]            [AppliedAmount]
282	2006-01-20 07:58:48.563	400.00
282	2006-01-20 09:03:30.360	400.00
278	2006-01-20 09:09:16.860	522.85

But, if i use the below statment to get all of the rows where applydate = '20060120', it returns 0 rows.

SELECT b.ID, ApplyDate ,b.AppliedAmount as AppliedAmt FROM [Audit] a join cas.CDData b on a.transactionid = b.transactionid
WHERE ((convert(datetime,[ApplyDate],103) between '20060120'and '20060120'))
and b.DocumentType = 2

I have to use ApplyDate between '20060120'and '20060121' to get the all of the rows where Applydate = '20060120'
SELECT b.ID, ApplyDate ,b.AppliedAmount as AppliedAmt FROM [Audit] a join cas.CDData b on a.transactionid = b.transactionid
WHERE ((convert(datetime,[ApplyDate],103) between '20060120'and '20060121'))
and b.DocumentType = 2

How can I fix this problem? why it doesn't picking up the records when I say ApplyDate between '20060120' and '20060120'?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-23 : 14:09:11

For my knowledge,

U'll get ur desired results by using ur query, only if the date has a time part as 00:00:00:000
If the date has a different time than above it assumes ApplyDate + t, which is not the same as ApplyDate.

So what do u want to do? Y do u want to go in that path only?
If u specifically need to deal with that, remove the time part and make that as what I put then u should be OK

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-01-23 : 15:51:51

yes, I want to go in that path only. Do you have a code example that I can take a look at it?

sqlnovice
Starting Member

10 Posts

Posted - 2006-01-23 : 16:43:00

See http://www.sql-server-performance.com/fk_datetime.asp

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-01-23 : 19:47:11

Go to BOL & Lear Convert(), Date / Time related functions and using those, get the date without time. Find the way to convert it to date part as normal and time part as 00:00:00:000
eg. 12/01/2005 03:18:27:231 ==> 12/01/2005 00:00:00:000

Then use that to get ur result.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-23 : 20:37:56

use >= '20060120'and < '20060121'. This will give you records that is between 2006-01-20 00:00:00 to 2006-01-20 23:59:59

SELECT b.ID, ApplyDate ,b.AppliedAmount as AppliedAmt 
FROM  [Audit] a join cas.CDData b on a.transactionid = b.transactionid
WHERE [ApplyDate] >= '20060120'
and   [ApplyDate] <  '20060121'
and   b.DocumentType   = 2

----------------------------------
'KH'
I do work from home but I don't do homework

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-24 : 01:05:47

Also refer
http://vyaskn.tripod.com/searching_date_time_values.htm

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources