Date Ranges - 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

General SQL Server Forums

New to SQL Server Programming

Date Ranges

Author

Topic

sohail.nawaz
Starting Member

3 Posts

Posted - 2015-04-28 : 13:03:27

Hello Experts,

Please see below my query, i want to filter the data on specified dates. (between dates).

*******************************************************
SELECT a.UserID, b.Name, b.EmployeeNum,b.Department, MIN(a.TransactionTime) AS TransactionTime,a.TransactionTime as TR_Date,
min(CONVERT(CHAR(20), TransactionTime, 113)) As TransactionDateTime
FROM NitgenAccessManager.dbo.NGAC_AUTHLOG AS a INNER JOIN NitgenAccessManager.dbo.NGAC_USERINFO AS b
ON a.UserID = b.ID
WHERE (a.FunctionKey = ' ')
AND CONVERT(VARCHAR(8), a.TransactionTime, 103) >= '01/04/2015' and CONVERT(VARCHAR(8), a.TransactionTime, 103)<= '28/04/2015'
AND a.TerminalID <> 60
AND b.expDate <> ('9999-01-01 00:00:00.000')
AND a.UserID = 20253
GROUP BY a.UserID, b.Name, b.EmployeeNum, b.Department, a.TransactionTime
Having min(CONVERT(CHAR(8), TransactionTime, 108))> '08:20:00'
order by TR_Date asc
***************************************************

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-28 : 13:22:29

Note that you are converting your dates to varchar(8) but the conversion type 103 returns ten characters.

BTW please follow these guidelines when posting:

http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

Forgot to mention, for the end of a date range, try 'mydate < targetdate+1 day'

e.g.

and a.TransactionTime > '20150401' and a.TransactionTime < '20150429'

sohail.nawaz
Starting Member

3 Posts

Posted - 2015-04-28 : 15:43:55

Thanks for your reply, it works. I want to take the minimum transaction per day.

UserID Name EmployeeNum Department TransactionDateTime
20253 George Verghase 02-253 Information Technology 13 Apr 2015 09:38:52
20253 George Verghase 02-253 Information Technology 13 Apr 2015 09:42:26
20253 George Verghase 02-253 Information Technology 13 Apr 2015 10:45:02
20253 George Verghase 02-253 Information Technology 13 Apr 2015 11:31:35
20253 George Verghase 02-253 Information Technology 13 Apr 2015 16:17:11
20253 George Verghase 02-253 Information Technology 13 Apr 2015 16:22:23

[quote]Originally posted by gbritton

Note that you are converting your dates to varchar(8) but the conversion type 103 returns ten characters.

BTW please follow these guidelines when posting:

http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

Forgot to mention, for the end of a date range, try 'mydate < targetdate+1 day'

e.g.

and a.TransactionTime > '20150401' and a.TransactionTime < '20150429'

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-28 : 15:58:39

"Thanks for your reply, it works"

By accident! Consider this part of your query:


CONVERT(VARCHAR(8), a.TransactionTime, 103) >= '01/04/2015' and CONVERT(VARCHAR(8), a.TransactionTime, 103)<= '28/04/2015'

Now check this:


select CONVERT(VARCHAR(8), cast('20000408' as datetime), 103)
select CONVERT(VARCHAR(8), cast('20100408' as datetime), 103)
select CONVERT(VARCHAR(8), cast('20200408' as datetime), 103)
select CONVERT(VARCHAR(8), cast('20300408' as datetime), 103)
select CONVERT(VARCHAR(8), cast('20400408' as datetime), 103)

These all produce the same result: 08/04/20

go ahead and try it.

"I want to take the minimum transaction per day."

Why didn't you mention that in the first place?

Also, please follow the posting guidelines (especially the CREATE TABLE and INSERT INTO statements) and include your sample result

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources