Using DateTime field in query - 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

Using DateTime field in query

Author

Topic

Sambasivam
Starting Member

36 Posts

Posted - 2009-07-14 : 10:34:01

I have table with DateTime Field. I need to fetch the field and corresponding value and display the dates as

Annually (Jan - Dec)
Semi Annually (Jan - Jun, Jul - Dec)
Weekly (Sunday, Month dd, yyyy - Satuday, Month dd, yyyy)
Daily
Hourly
formats. Any help will be appreciated.I need this urgently. Please help me.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-14 : 10:35:35

you need to provide more information like your table structure, sample data and how do you want the result

KH
[spoiler]Time is always against us[/spoiler]

Sambasivam
Starting Member

36 Posts

Posted - 2009-07-14 : 10:43:08

hope this sample spreadhsheet will be helpful

ActivityTypeID ActivityHistoryID PortfolioID ActivityDateTime
1 32 30 12/6/00 3:15 PM
1 33 31 12/6/00 4:45 PM here 12/6/00 to 12/13/00 will be a week
1 34 32 12/6/00 4:47 PM so I need the query to display the ActivityDate time weekwise.
1 35 33 12/6/00 4:52 PM
1 36 35 12/6/00 4:59 PM similarly for
1 37 36 12/6/00 5:06 PM Annually (Jan - Dec)
1 39 39 12/7/00 9:07 AM Semi Annually (Jan - Jun, Jul - Dec)
1 40 45 12/7/00 10:26 AM Daily
1 41 50 12/7/00 10:41 AM Hourly
1 42 47 12/7/00 10:49 AM
1 43 54 12/7/00 11:04 AM
1 44 55 12/7/00 11:06 AM
1 45 56 12/7/00 11:07 AM
1 46 53 12/7/00 11:09 AM
1 47 57 12/7/00 12:28 PM
1 48 58 12/7/00 12:41 PM
1 49 59 12/8/00 9:00 AM
1 50 60 12/8/00 9:37 AM
1 51 61 12/8/00 10:00 AM
1 52 62 12/8/00 10:26 AM
1 53 63 12/8/00 10:50 AM
1 54 63 12/8/00 10:54 AM
1 55 66 12/8/00 11:13 AM
1 56 68 12/8/00 11:17 AM
1 57 67 12/8/00 11:17 AM
1 58 69 12/8/00 11:31 AM
1 59 71 12/8/00 11:56 AM
1 60 72 12/8/00 12:09 PM
1 61 73 12/8/00 12:33 PM
1 62 77 12/8/00 12:43 PM
1 63 79 12/8/00 1:07 PM
1 64 78 12/8/00 1:09 PM
1 65 80 12/8/00 1:20 PM
1 66 81 12/8/00 1:36 PM
1 67 83 12/8/00 1:49 PM
1 68 84 12/8/00 1:57 PM
1 69 85 12/8/00 2:02 PM
1 70 83 12/8/00 2:05 PM
1 71 86 12/8/00 2:06 PM
1 72 83 12/8/00 2:11 PM
1 73 87 12/8/00 2:36 PM
1 74 88 12/8/00 2:54 PM
1 75 89 12/8/00 3:28 PM
1 76 90 12/8/00 3:37 PM
1 77 91 12/8/00 3:41 PM
1 78 93 12/8/00 4:26 PM
1 79 103 12/8/00 4:56 PM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-14 : 10:45:08

so what's the expected result ?

KH
[spoiler]Time is always against us[/spoiler]

Sambasivam
Starting Member

36 Posts

Posted - 2009-07-14 : 10:47:03

these dates should be qqueried as annaully, semi-annually, weekly, hourly

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-14 : 10:49:50

quote:
Originally posted by Sambasivam

these dates should be qqueried as annaully, semi-annually, weekly, hourly

Sorry . . i am totally lost at what you want here. How does the required result looks like ?

KH
[spoiler]Time is always against us[/spoiler]

Sambasivam
Starting Member

36 Posts

Posted - 2009-07-14 : 10:53:57

Annually
=========
Period Field1 Field2 [ Field1 and Field2 are from different tables]
January - December xxx xxx

Semi Annually
==============
Period Field1 Field2 [ Field1 and Field2 are from different tables]
January - June xxx xxx
July - December xxx xxx

Weekly
=======
Period Field1 Field2 [ Field1 and Field2 are from different tables]
Sunday Jan 01,2008 - Saturday Jan 07,2008 xxx xxx
Sunday Jan 08,2008 - Saturday Jan 15,2008 xxx xxx

hope this helps

Sambasivam
Starting Member

36 Posts

Posted - 2009-07-14 : 13:31:34

Ok I got query to do something similar but still needs some changes to reflect my expectation.

Select PS.PriceSourceName,Sum(PSU.NumberEntriesPriced) AS Entries,
right('0'+convert(varchar(2),datepart(ww,(DateTime))),2) As [Week],datename(weekday,DateTime) As [WeekDay],
convert(varchar(15),DateName(month,DateTime)) + ',' + right('0'+convert(varchar(2),day(DateTime)),2)
+ ',' + convert(varchar(4),year(DateTime)) As [Month , Day, Year]
from PricingSourceUsage PSU
Inner Join PriceSource PS ON PS.PriceSourceID = PSU.PriceSourceID
Inner Join
(Select ActivityHistoryID As [ActID],Convert(Varchar(10),ActivityDateTime,111) AS DateTime from ActivityHistory Where Convert(Varchar(10),ActivityDateTime,111)
Between '2009/01/01' And '2009/12/31' AND PortfolioID IN (Select Distinct PortfolioID from ActivityHistory
Where Convert(Varchar(10),ActivityDateTime,111) Between '2009/01/01' And '2009/12/31')) ActivityData
On ActivityData.ActID = PSU.ActivityHistoryID
Group By PS.PriceSourceName,Convert(Varchar(10),ActivityData.DateTime,111),DateName(WEEKDAY,ActivityData.DateTime)
Order By ActivityData.DateTime,DateName(WEEKDAY,ActivityData.DateTime) desc

will give result as

PriceSourceName Entries Week WeekDay Month , Day, Year
FT Interactive 35 02 Sunday January,04,2009
Reuters 3 02 Sunday January,04,2009
Reuters 1549 02 Monday January,05,2009
FT Interactive 13791 02 Monday January,05,2009
FT Interactive 11261 02 Tuesday January,06,2009
Reuters 7319 02 Tuesday January,06,2009
Reuters 7786 02 Wednesday January,07,2009
FT Interactive 9117 02 Wednesday January,07,2009
FT Interactive 4401 02 Thursday January,08,2009
Reuters 2604 02 Thursday January,08,2009
Reuters 16878 02 Friday January,09,2009
FT Interactive 21262 02 Friday January,09,2009
FT Interactive 4056 02 Saturday January,10,2009
Reuters 1052 02 Saturday January,10,2009
Reuters 1164 03 Sunday January,11,2009
FT Interactive 1904 03 Sunday January,11,2009
FT Interactive 23776 03 Monday January,12,2009
Reuters 14030 03 Monday January,12,2009
Reuters 43336 03 Tuesday January,13,2009
FT Interactive 61360 03 Tuesday January,13,2009
FT Interactive 21411 03 Wednesday January,14,2009
Reuters 8939 03 Wednesday January,14,2009
Reuters 23538 03 Thursday January,15,2009
FT Interactive 41205 03 Thursday January,15,2009
FT Interactive 14673 03 Friday January,16,2009
Reuters 7043 03 Friday January,16,2009
Reuters 1968 03 Saturday January,17,2009
FT Interactive 23924 03 Saturday January,17,2009
FT Interactive 4920 04 Sunday January,18,2009
Reuters 9430 04 Sunday January,18,2009
Reuters 23157 04 Monday January,19,2009
FT Interactive 13318 04 Monday January,19,2009
FT Interactive 23488 04 Tuesday January,20,2009
Reuters 11229 04 Tuesday January,20,2009
Reuters 17724 04 Wednesday January,21,2009
FT Interactive 26011 04 Wednesday January,21,2009
FT Interactive 33729 04 Thursday January,22,2009
Reuters 18836 04 Thursday January,22,2009
Reuters 23740 04 Friday January,23,2009
FT Interactive 24401 04 Friday January,23,2009
FT Interactive 23839 04 Saturday January,24,2009
Reuters 2777 04 Saturday January,24,2009
Reuters 961 05 Sunday January,25,2009
FT Interactive 1019 05 Sunday January,25,2009
FT Interactive 19896 05 Monday January,26,2009
Reuters 11256 05 Monday January,26,2009
Reuters 11306 05 Tuesday January,27,2009
FT Interactive 15997 05 Tuesday January,27,2009
FT Interactive 25918 05 Wednesday January,28,2009
Reuters 12446 05 Wednesday January,28,2009
Reuters 7054 05 Thursday January,29,2009
FT Interactive 9469 05 Thursday January,29,2009
FT Interactive 7307 05 Friday January,30,2009
Reuters 10179 05 Friday January,30,2009
Reuters 831 05 Saturday January,31,2009
FT Interactive 2875 05 Saturday January,31,2009
FT Interactive 1783 06 Sunday February,01,2009
Reuters 881 06 Sunday February,01,2009
Reuters 2046 06 Monday February,02,2009
FT Interactive 16474 06 Monday February,02,2009
FT Interactive 29253 06 Thursday February,05,2009
Reuters 3552 06 Thursday February,05,2009
Reuters 1860 06 Friday February,06,2009
FT Interactive 10813 06 Friday February,06,2009
FT Interactive 2410 06 Saturday February,07,2009
Reuters 88 06 Saturday February,07,2009
Reuters 1679 07 Sunday February,08,2009
FT Interactive 855 07 Sunday February,08,2009
FT Interactive 32317 07 Monday February,09,2009
Reuters 8253 07 Monday February,09,2009
Reuters 2911 07 Tuesday February,10,2009
FT Interactive 11073 07 Tuesday February,10,2009
FT Interactive 18807 07 Wednesday February,11,2009
Reuters 17750 07 Wednesday February,11,2009
Reuters 8801 07 Thursday February,12,2009
FT Interactive 7410 07 Thursday February,12,2009
FT Interactive 3155 07 Friday February,13,2009
Reuters 1491 07 Friday February,13,2009
Reuters 264 07 Saturday February,14,2009
FT Interactive 221 07 Saturday February,14,2009
FT Interactive 547 08 Sunday February,15,2009
Reuters 9878 08 Sunday February,15,2009
Reuters 7195 08 Monday February,16,2009
FT Interactive 7388 08 Monday February,16,2009
FT Interactive 16943 08 Tuesday February,17,2009
Reuters 7621 08 Tuesday February,17,2009
Reuters 5358 08 Wednesday February,18,2009
FT Interactive 9113 08 Wednesday February,18,2009
FT Interactive 5872 08 Thursday February,19,2009
Reuters 2494 08 Thursday February,19,2009
Reuters 6985 08 Friday February,20,2009
FT Interactive 12976 08 Friday February,20,2009
FT Interactive 1064 08 Saturday February,21,2009
Reuters 4080 08 Saturday February,21,2009
Reuters 670 09 Sunday February,22,2009
FT Interactive 254 09 Sunday February,22,2009
FT Interactive 8676 09 Monday February,23,2009
Reuters 11934 09 Monday February,23,2009
Reuters 6618 09 Tuesday February,24,2009
FT Interactive 15491 09 Tuesday February,24,2009
FT Interactive 1861 09 Wednesday February,25,2009
Reuters 2499 09 Wednesday February,25,2009
Reuters 1595 09 Thursday February,26,2009
FT Interactive 1159 09 Thursday February,26,2009
FT Interactive 17274 11 Tuesday March,10,2009
Reuters 10879 11 Tuesday March,10,2009
Reuters 13158 11 Wednesday March,11,2009
FT Interactive 20645 11 Wednesday March,11,2009
FT Interactive 15808 11 Thursday March,12,2009
Reuters 4171 11 Thursday March,12,2009
Reuters 360 11 Friday March,13,2009
FT Interactive 3045 11 Friday March,13,2009
FT Interactive 1527 11 Saturday March,14,2009
Reuters 541 11 Saturday March,14,2009
Reuters 35 12 Sunday March,15,2009
FT Interactive 555 12 Sunday March,15,2009
FT Interactive 3443 12 Monday March,16,2009
Reuters 5622 12 Monday March,16,2009
Reuters 15295 12 Tuesday March,17,2009
FT Interactive 4214 12 Tuesday March,17,2009
FT Interactive 3676 12 Wednesday March,18,2009
Reuters 1688 12 Wednesday March,18,2009
Reuters 635 12 Thursday March,19,2009
FT Interactive 1250 12 Thursday March,19,2009
FT Interactive 1059 12 Friday March,20,2009
Reuters 5313 12 Friday March,20,2009
Reuters 217 12 Saturday March,21,2009
FT Interactive 845 12 Saturday March,21,2009
FT Interactive 536 13 Sunday March,22,2009
Reuters 6 13 Sunday March,22,2009
Reuters 451 13 Monday March,23,2009
FT Interactive 523 13 Monday March,23,2009
FT Interactive 2991 13 Tuesday March,24,2009
Reuters 2547 13 Tuesday March,24,2009
Reuters 15341 13 Wednesday March,25,2009
FT Interactive 7519 13 Wednesday March,25,2009
FT Interactive 1501 13 Thursday March,26,2009
Reuters 1138 13 Thursday March,26,2009
Reuters 526 13 Friday March,27,2009
FT Interactive 414 13 Friday March,27,2009
FT Interactive 150 13 Saturday March,28,2009
Reuters 0 13 Saturday March,28,2009
Reuters 413 14 Sunday March,29,2009
FT Interactive 79 14 Sunday March,29,2009
FT Interactive 2386 14 Monday March,30,2009
Reuters 1363 14 Monday March,30,2009
Reuters 1074 14 Tuesday March,31,2009
FT Interactive 734 14 Tuesday March,31,2009
FT Interactive 19263 15 Monday April,06,2009
Reuters 1654 15 Monday April,06,2009
Reuters 6061 15 Tuesday April,07,2009
FT Interactive 16814 15 Tuesday April,07,2009
FT Interactive 6883 15 Wednesday April,08,2009
Reuters 2524 15 Wednesday April,08,2009
Reuters 2329 15 Thursday April,09,2009
FT Interactive 2073 15 Thursday April,09,2009
FT Interactive 7148 15 Friday April,10,2009
Reuters 519 15 Friday April,10,2009
Reuters 12 16 Sunday April,12,2009
FT Interactive 3 16 Sunday April,12,2009
FT Interactive 3201 16 Monday April,13,2009
Reuters 6272 16 Monday April,13,2009
Reuters 4939 16 Tuesday April,14,2009
FT Interactive 12261 16 Tuesday April,14,2009
FT Interactive 5938 16 Wednesday April,15,2009
Reuters 2253 16 Wednesday April,15,2009
Reuters 3969 16 Thursday April,16,2009
FT Interactive 2646 16 Thursday April,16,2009
FT Interactive 2105 16 Friday April,17,2009
Reuters 3318 16 Friday April,17,2009
Reuters 0 17 Sunday April,19,2009
FT Interactive 76 17 Sunday April,19,2009
FT Interactive 4323 17 Monday April,20,2009
Reuters 285 17 Monday April,20,2009
Reuters 2615 17 Tuesday April,21,2009
FT Interactive 3489 17 Tuesday April,21,2009
FT Interactive 1366 17 Wednesday April,22,2009
Reuters 195 17 Wednesday April,22,2009
Reuters 30 17 Thursday April,23,2009
FT Interactive 758 17 Thursday April,23,2009
FT Interactive 14224 17 Friday April,24,2009
Reuters 868 17 Friday April,24,2009
Reuters 8 17 Saturday April,25,2009
FT Interactive 23 17 Saturday April,25,2009
FT Interactive 2704 18 Sunday April,26,2009
Reuters 2602 18 Sunday April,26,2009
Reuters 740 18 Monday April,27,2009
FT Interactive 317 18 Monday April,27,2009
FT Interactive 1323 18 Tuesday April,28,2009
Reuters 4336 18 Tuesday April,28,2009
Reuters 5004 18 Wednesday April,29,2009
FT Interactive 7321 18 Wednesday April,29,2009
FT Interactive 738 18 Thursday April,30,2009
Reuters 2956 18 Thursday April,30,2009
Reuters 6 19 Wednesday May,06,2009
FT Interactive 243 19 Wednesday May,06,2009
FT Interactive 3945 19 Thursday May,07,2009
Reuters 2624 19 Thursday May,07,2009
Reuters 2066 19 Friday May,08,2009
FT Interactive 2019 19 Friday May,08,2009
FT Interactive 108 19 Saturday May,09,2009
Reuters 588 19 Saturday May,09,2009
Reuters 1 20 Sunday May,10,2009
FT Interactive 580 20 Sunday May,10,2009
FT Interactive 4125 20 Monday May,11,2009
Reuters 5919 20 Monday May,11,2009
Reuters 687 20 Tuesday May,12,2009
FT Interactive 4487 20 Tuesday May,12,2009
FT Interactive 3905 20 Wednesday May,13,2009
Reuters 4060 20 Wednesday May,13,2009
Reuters 4384 20 Thursday May,14,2009
FT Interactive 3658 20 Thursday May,14,2009
FT Interactive 2521 20 Friday May,15,2009
Reuters 534 20 Friday May,15,2009
Reuters 101 21 Sunday May,17,2009
FT Interactive 71 21 Sunday May,17,2009
FT Interactive 2362 21 Monday May,18,2009
Reuters 2660 21 Monday May,18,2009
Reuters 1976 21 Tuesday May,19,2009
FT Interactive 1627 21 Tuesday May,19,2009
FT Interactive 2848 21 Wednesday May,20,2009
Reuters 2154 21 Wednesday May,20,2009
Reuters 4802 21 Thursday May,21,2009
FT Interactive 2622 21 Thursday May,21,2009
FT Interactive 23 21 Friday May,22,2009
Reuters 1 21 Friday May,22,2009
Reuters 69 22 Monday May,25,2009
FT Interactive 1 22 Monday May,25,2009
FT Interactive 1244 22 Tuesday May,26,2009
Reuters 812 22 Tuesday May,26,2009
Reuters 2356 22 Wednesday May,27,2009
FT Interactive 825 22 Wednesday May,27,2009
FT Interactive 795 22 Thursday May,28,2009
Reuters 10 22 Thursday May,28,2009
Reuters 810 22 Friday May,29,2009
FT Interactive 239 22 Friday May,29,2009
FT Interactive 68 23 Friday June,05,2009
Reuters 91 23 Friday June,05,2009

Now I want the columns Week, WeekDay, Month,Day,Year to be replaced with one Field which says Sunday January,04,2009 - Saturday January,10,2009 for first week (first 13 rows in the query result). any help pls

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-14 : 14:30:38

the best way to do above kind of query is to have a calendar table with dates and other supoplymentary info like week,month,period,quarter,halfyear & year which date falls in. then its just a matter of joining onto this table on date field and grouping on required field like year, halfyear, week,..

Sambasivam
Starting Member

36 Posts

Posted - 2009-07-14 : 14:54:32

I cant create table or influence in anyway to create a table separately for this purpose.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-15 : 13:21:20

then why dont you generate a temporary table with those calendard columns based on date field using date functions like datepart

Sambasivam
Starting Member

36 Posts

Posted - 2009-07-15 : 13:23:38

Im very new to Sql and dnt have much stuff left to try out this option.any idea further is appreciable.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-15 : 13:31:25

some thin like


SELECT columns,YEAR(datefield) AS Year,MONTH(datefield) AS Month,DATEPART(wk, datefield) AS Week,
CASE WHEN MONTH(datefield) IN (1,2,3) THEN 'Q1'
     WHEN MONTH(datefield) IN (4,5,6) THEN 'Q2'
     WHEN MONTH(datefield) IN (7,8,9) THEN 'Q3'
     WHEN MONTH(datefield) IN (10,11,12) THEN 'Q4'
END AS Quarter,
CASE WHEN MONTH(datefield) IN (1,2,3,4,5,6) THEN 'H1'
     WHEN MONTH(datefield) IN (7,8,9,10,11,12) THEN 'H2'
END AS HalfYear,
...
INTO #Temp
FROM YourCurrentTable

then in your final select use like

SELECT fields...
FROM #temp
...
GROUP BY CASE @Grouping
         WHEN 'Annualy' THEN Year
         WHEN 'Semi Annually' THEN HalfYear
....
         END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources