| Author |
Topic |
|
flextera
Yak Posting Veteran
50 Posts |
 Posted - 2014-09-11 : 11:52:02
|
| I have created a report in visual studio 2013 that talks with my SQL server through data connections. I want to be able to query my report BY HOUR. Meaning, I want the report to gather values at 14:30, 22:30, and 06:30. Here is my nonworking code so far. AND DATEPART(hh,[datetime]) = 0630 AND DATEPART(hh, [datetime]) = 1430 AND DATEPART(hh, [datetime]) = 2230 |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-09-11 : 12:30:50
|
| Well, the clause you posted can never be true, can it? That is, at what hour of the day is the hour equal to 0620, 1430 and 2230? |
 |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2014-09-11 : 12:35:42
|
| and datepart(hh,[datetime]) in (0630,1430,2230) |
|
|
|
flextera
Yak Posting Veteran
50 Posts |
Posted - 2014-09-11 : 12:38:56
|
| I need it for 6:30 a.m. 2:30 p.m. and 10:30 p.m.My Times look like: 08:59:58.4240000 |
|
|
|
flextera
Yak Posting Veteran
50 Posts |
Posted - 2014-09-11 : 12:43:35
|
| @bitsmed When I use your code my Visual Studio forum comes back blank? |
|
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2014-09-11 : 12:45:22
|
| Sorry.and datepart(hh,[datetime])*100+datepart(mi,[datetime]) in (0630,1430,2230) |
|
|
|
flextera
Yak Posting Veteran
50 Posts |
Posted - 2014-09-11 : 12:49:08
|
| Okay. Here is my next question. This query is bringing me values for ALL of 2,6,11. I only want ONE value at exactly 2. 6. and 11. |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-09-11 : 15:18:52
|
| What do you mean by "ALL of 2,6,11"? Perhaps you should post your entire query and some of the wrong output |
|
|
|
flextera
Yak Posting Veteran
50 Posts |
Posted - 2014-09-11 : 15:28:40
|
| It's returing values for 2:01, 2:02, 2:03 etc. I ONLY want ONE value at 2 P.M. 6 P.M. and 11 P.M. Here is the code I am am working with. AND datepart(hh,[datetime]) in (6,2,11) |
|
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2014-09-11 : 15:34:48
|
quote:
Originally posted by flextera
It's returing values for 2:01, 2:02, 2:03 etc. I ONLY want ONE value at 2 P.M. 6 P.M. and 11 P.M. Here is the code I am am working with. AND datepart(hh,[datetime]) in (6,2,11)
use this: and datepart(hh,[datetime])*100+datepart(mi,[datetime]) in (0200,0600,1100)or this: and datepart(hh,[datetime]) in (02,06,11) and datepart(mi,[datetime])=00 |
|
|
|
flextera
Yak Posting Veteran
50 Posts |
Posted - 2014-09-11 : 16:04:57
|
| This ALMOST works. It is still adding the seconds to the calculation. Is there a way to exclude the seconds? |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-09-11 : 16:59:11
|
| Group by the hourPlease post your whole query |
|
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2014-09-12 : 00:40:30
|
quote:
Originally posted by flextera
This ALMOST works. It is still adding the seconds to the calculation. Is there a way to exclude the seconds?
use this: and datepart(hh,[datetime])*10000+datepart(mi,[datetime])*100+datepart(ss,[datetime]) in (020000,060000,110000)or this: and datepart(hh,[datetime]) in (02,06,11) and datepart(mi,[datetime])=00 and datepart(ss,[datetime])=00 |
|
|
|
flextera
Yak Posting Veteran
50 Posts |
Posted - 2014-09-12 : 09:02:09
|
| I really appreciate all your help. You seem to have SQL queries down to a science ;) This question is resolved. Thanks for your time and patients! |
|
|
|
|
Query SQL report by HOUR(RESOLVED)