Selecting the records in the present week

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

Selecting the records in the present week

Author

Topic

prakashuj
Starting Member

12 Posts

Posted - 2007-12-03 : 06:19:00

Dear All,

I want to retrive the records if are created in a particular week.
if i pass 03/12/2007 , i want get records which are created in that week

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-03 : 06:21:38

[code]Select * from Table
Where Datepart(week, date_created) = datepart(week, '20071203')[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-03 : 07:53:26

quote:
Originally posted by harsh_athalye

Select * from Table
Where Datepart(week, date_created) = datepart(week, '20071203')

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

select datepart(week, '20071203'),datepart(week, '20061203')

Madhivanan

Failing to plan is Planning to fail

prakashuj
Starting Member

12 Posts

Posted - 2007-12-03 : 07:55:30

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-03 : 07:57:35

[code]Select * from Table
Where Datepart(week, date_created) = datepart(week, '20071203')
and Datepart(year, date_created) = 2007[/code]

Thanks Madhi !

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

prakashuj
Starting Member

12 Posts

Posted - 2007-12-03 : 22:42:13

Hi,

I want the output in this way

Days No of.Requests
Mon 5
Tue 6
Wed 7
Thu 3
Fri 2
Sat 1

I will pass only the date to the query
Pls help me to solve this problem

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-04 : 00:51:40

select datename(weekday,datecol) as days,count(*) as no_of_request from table
group by datename(weekday,datecol)

Madhivanan

Failing to plan is Planning to fail

prakashuj
Starting Member

12 Posts

Posted - 2007-12-05 : 04:59:42

Dear Madhivanan
Thanx for your help.

I Have one more problem , help me to reslove it.
In my Table there are a records

PId DateTime State
497 2007-08-06 09:48:29.043 2 9
497 2007-08-06 09:46:55.040 2 9

prakashuj
Starting Member

12 Posts

Posted - 2007-12-05 : 05:03:47

Dear Madhivanan
Thanx for your help.

I Have one more problem , help me to reslove it.
In my Table there are a records

PId DateTime State
497 2007-08-06 09:48:29.043 CREATED
497 2007-08-06 09:46:55.040 CREATED

Select Datepart(hh,Min(DateTime)),Count(*) 'Total'
From TAB
WHERE State='Created'
And Datepart(WEEK,CONVERT(DATETIME,CONVERT(VARCHAR(10),DateTime,101))) = Datepart(WEEK,CONVERT(DATETIME,'10/08/2007',103))
AND Datepart(dw,DateTime)=2
And PID=497
Group By Datepart(hh,[$DateTime])

I should get only one record with min datetime
but the query is returing count as 2

Pls help me to resolve this problem

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-05 : 07:16:15

Can you post some more sample data with expected result?

Madhivanan

Failing to plan is Planning to fail

prakashuj
Starting Member

12 Posts

Posted - 2007-12-05 : 10:35:47

I Want to list the records with min dateTime ,

Select Distinct Vw_OERequests.FormCode 'Form Code',vw_Audit_HBLRequest.FormCode 'HBL No',Vw_OERequests.JobNumber,
vw_Audit_HBLRequest.[$DateTime]
From vw_Audit_HBLRequest
Inner JOIN vw_OERequests on vw_OERequests.HBLId=vw_Audit_HBLRequest.FormId
WHERE vw_Audit_HBLRequest.StateId='AgIndCoeHBLCreated'
AND Datepart(WEEK,CONVERT(DATETIME,CONVERT(VARCHAR(10),vw_Audit_HBLRequest.[$DateTime],101))) = Datepart(WEEK,CONVERT(DATETIME,'10/08/2007',103))
AND Datepart(dw,[$DateTime])=6
AND Datepart(hh,[$DateTime]) =19
Result
Formcode HBlNo JobNumb
E-497 BL-119 BXS1 2007-08-10 19:24:35.703
E-497 BL-119 BXS1 2007-08-10 19:24:38.390
E-531 BL-118 CXS1 2007-08-10 19:14:15.343
E-531 BL-118 CXS1 2007-08-10 19:14:18.250
E-684 BL-117 BXS1 2007-08-10 19:11:48.443
E-684 BL-117 BXS1 2007-08-10 19:11:50.850
OutPut
E-497 BL-119 BXS1 2007-08-10 19:24:38.390
E-531 BL-118 CXS1 2007-08-10 19:14:15.343
E-684 BL-117 BXS1 2007-08-10 19:11:48.443

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 10:45:37

Is this the same question?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93718

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources