| Author |
Topic |
|
janetb
Yak Posting Veteran
71 Posts |
 Posted - 2005-11-21 : 14:11:52
|
| Rather than reinvent the wheel, can someone pass along a date/time function combination? I have a field of smallDateTime being used primarily for the time value - all rows have the same date. e.g., format is 10/22/2005 01:00:00 PM.What's the easiest function combination to get all rows for day (then night) where day is defined as 6am-10pm (night is midnight-6am and 10pm-midnight). Thought I could use datepart and hh, but it doesn't give me 1 pm as the true miliary time of 13.Many thanks. |
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2005-11-21 : 14:26:15
|
Can you further explain what you mean by? quote:
to get all rows for day (then night)
Does that mean that you want them lumped together in 1 result set, ordered by day hours first and then by night hours after that? Or what? |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-11-21 : 14:30:41
|
Try this:where -- 6 am to 10 pm MySmallDTCol >= '20051022 06:00:00.000' and MySmallDTCol < '20051022 22:00:00.000'where -- midnight to 6 am ( MySmallDTCol >= '20051022 00:00:00.000' and MySmallDTCol < '20051022 06:00:00.000' ) or -- 10 pm to midnight ( MySmallDTCol >= '20051022 22:00:00.000' and MySmallDTCol < '20051023 00:00:00.000' ) CODO ERGO SUM |
|
|
|
janetb
Yak Posting Veteran
71 Posts |
Posted - 2005-11-21 : 14:32:55
|
| Sure, and thanks druer.I may have 20 rows, but I want to do some aggregate adding based upon whether the myTime row entry is day or night as defined above. For example, column 1 might be myVol, column2 myTime. I would want to add all myVol for day, all myVol for night and then have a full total.Does that help? |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2005-11-21 : 14:50:15
|
| You can try:select sum(case when datepart(hh, mydate) between 6 and 21 then myval else 0 end) as DayTotal, sum(case when not datepart(hh, mydate) between 6 and 21 then myval else 0 end) as NightTotal,sum(myval) as CompleteTotalfrom YourTableYour initial question indicated that all of the date values were the same day so I didn't try to do any group by for the data value. |
|
|
|
janetb
Yak Posting Veteran
71 Posts |
Posted - 2005-11-21 : 14:56:13
|
| No good. That's whay I included the format. hh doesn't give me 1:00 pm. And, yes, you're right in your assumption - all have the same date for date portion of the field. I was looking for a combination of functions that would return the equivalent of perhaps military time so that 1:00 pm would be captured as 13 and not 1?Make sense? |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2005-11-21 : 15:04:31
|
| In my test environment with a smalldatetime I can input the value as either 1..PM or as 13 and regardless of my input the value goes in as 13. When I do a select DATEPART(HH, MyDateField) I get the value back as 13. Thus my query gives you a DayTotal from 6 which would be anything from 6:00 AM until 9:59 PM which is 21. Then for night values I simply exclude the values I used for day. What am I missing? |
|
|
|
janetb
Yak Posting Veteran
71 Posts |
Posted - 2005-11-21 : 15:11:38
|
| Nada - you're fine. My sister dropped me on my head when I was an infant. Many apologies. Thanks for your time and patience. |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2005-11-21 : 15:14:05
|
| lol. At least you blamed your sister and not your mom like most people do. Glad I could help. |
|
|
|
|
select smalldatetime