Date Functions SQL - 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 Functions SQL

Author

Topic

thehandsomecode
Yak Posting Veteran

51 Posts

Posted - 2007-05-07 : 01:04:47

I need some help with some date functions:
this is how they are stored in the database '5/4/2007 1:00:00 PM'
if i were to query a list of all records with todays date, how do i achieve that.
also if i were to set a query to fetch all records within a two week date range how do i so that

Melvin Felicien
IT Manager
DCG Properties Limited

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-07 : 01:32:45

quote:
how they are stored in the database '5/4/2007 1:00:00 PM'

The Query should be like this


Select * From YourTable Where Convert(Varchar(10),YourDateColumn,112) = Convert(Varchar(10),GetDate(),112)

quote:
f i were to set a query to fetch all records within a two week date range how do i so that


Select * From YourTable Where YourDateColumn >= DateAdd(dd,-14,Getdate())And YourDateColumn <=DateAdd(dd,1,GetDate())

Chirag

http://chirikworld.blogspot.com/

thehandsomecode
Yak Posting Veteran

51 Posts

Posted - 2007-05-07 : 02:40:13

Thank you. what if i needed to total the amount of records returned per category. how would i do that.
the database contains a set of tasks performed by different people and i need to be able to total per person, based on a given date / date range how many or each task were completed. tasks are either Phone call, Email etc..

Melvin Felicien
IT Manager
DCG Properties Limited

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-07 : 02:44:07

Select category, count(category) from <table name> where <condition> group by categorgy

thehandsomecode
Yak Posting Veteran

51 Posts

Posted - 2007-05-07 : 02:56:00

shouldnt i be using the sum (case when)?
by the way is there someone i can pay to write some short sql code for me? nothing complicated.

Melvin Felicien
IT Manager
DCG Properties Limited

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-07 : 02:58:04

put some sample data and ur required o/p

thehandsomecode
Yak Posting Veteran

51 Posts

Posted - 2007-05-07 : 03:05:42

you can download some sample code here
http://208.0.225.33/frd/dir/ddload/samplecode.zip
this is a list of activities carried out by vaious people. i need to sort out or create a report to show activities completed today. the datafields "statuscodename = complete" and "actualend" date being whatever date the report is run.
Pleae help

Melvin Felicien
IT Manager
DCG Properties Limited

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-07 : 03:43:57

In the Zip file i didnt found any records with the todays date, so i applied the filter for the 2 weeks..

check the output if you want somthing like this


Select Count(1) 'Task Completed', ActivityTypeCodeName,CreatedByName From 
TestingDemo 
Where 
statuscodename = 'Completed' 
And actualend >= DateAdd(dd,-14,Getdate()) And actualend <=DateAdd(dd,1,GetDate()) 
--And Convert(Varchar(10),actualend ,112) = Convert(Varchar(10),GetDAte(),112)
Group by 
ActivityTypeCodeName,CreatedByName

PS: Replace TestingDemo Tablename with your tablename. !!
Chirag

http://chirikworld.blogspot.com/

thehandsomecode
Yak Posting Veteran

51 Posts

Posted - 2007-05-07 : 06:42:25

Wow!, you guys are good. thank you so much. so far this works perfectly. do you happen to any knowledge about sql reporting services as well. cause i would love to make the dates filterable to the user can select a custom date range.
i will complete the query and let you know how every works out.

Melvin Felicien
IT Manager
DCG Properties Limited

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-07 : 06:51:48

Well, you can post you doubt under SQL Reporting Service forum, surely some over here can help you with it..!!

Chirag

http://chirikworld.blogspot.com/

thehandsomecode
Yak Posting Veteran

51 Posts

Posted - 2007-05-07 : 06:54:25

The query doesnt quite return the data how i expect this. to be a bit more precise this is what i am hopeing to be returned

Createdby PhoneCall Email Fax
--------- ---------- ------- -------
Adam 7 5 2
carol 54 6 1

Please help

Melvin Felicien
IT Manager
DCG Properties Limited

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-07 : 07:00:55

SELECT CreatedByName, SUM(CASE WHEN ActivityTypeCodeName = 'Phone Call' THEN 1 ELSE 0 END) AS PhoneCall,
SUM(CASE WHEN ActivityTypeCodeName = 'E-mail' THEN 1 ELSE 0 END) AS Email,
SUM(CASE WHEN ActivityTypeCodeName = 'Fax' THEN 1 ELSE 0 END) AS Fax
FROM Table1
WHERE ActualEnd >= '20070507' AND ActualEnd < '20070508'
GROUP BY CreatedByName
ORDER BY CreatedByName

Peter Larsson
Helsingborg, Sweden

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-07 : 07:05:19

[code]
Select CreatedByName,
Sum( Case When activitytypecode = 4202 Then 1 Else 0 End ) 'E-mail' ,
Sum( Case When activitytypecode = 4210 Then 1 Else 0 End ) 'Phone Calll' ,
Sum( Case When activitytypecode = 4212 Then 1 Else 0 End ) 'Task' ,
Sum( Case When activitytypecode = 4406 Then 1 Else 0 End ) 'Bulk Operationl'
From
TestingDemo
Where
statuscodename = 'Completed'
And actualend >= DateAdd(dd,-14,Getdate()) And actualend <=DateAdd(dd,1,GetDate())
--And Convert(Varchar(10),actualend ,112) = Convert(Varchar(10),GetDAte(),112)
Group by
ActivityTypeCodeName,CreatedByName

[/code]

Chirag

http://chirikworld.blogspot.com/

thehandsomecode
Yak Posting Veteran

51 Posts

Posted - 2007-05-07 : 07:29:57

Thank you all. this works great. i will be back for more help once my directors try to force some reports out of an sql idiot like me. thank you for you help

Melvin Felicien
IT Manager
DCG Properties Limited

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources