Ignore Time Stamp in the Following:

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

Ignore Time Stamp in the Following:

Author

Topic

rnelsch
Starting Member

31 Posts

Posted - 2012-08-23 : 15:47:11

I have the following query built

Select Project.LeadSource,
ClosedDate, Sum (Job.TotalTicket)
From Job
Inner Join Project On Job.ProjectID = Project.ProjectID
Where Job.ClosedDate >= '1/1/12'
Group by Project.LeadSource, Job.ClosedDate

But I need to ignore the time stamp on the Job.ClosedDate column. Here is what I have tried.

Select Project.LeadSource, Conver (Varchar(10), ClosedDate, 101), Sum (Job.TotalTicket)
From Job
Inner Join Project On Job.ProjectID = Project.ProjectID
Where Job.ClosedDate >= '1/1/12'
Group by Project.LeadSource, Job.ClosedDate

But it doesn't seem to work. It is telling me "varchar" is not recognized, but I know I've used it in other queries?

Ryan A Nelsch

chadmat
The Chadinator

1974 Posts

Posted - 2012-08-23 : 16:07:16

ConverT...don't forget the T

-Chad

rnelsch
Starting Member

31 Posts

Posted - 2012-08-23 : 16:29:14

I noticed that as soon as I hit post topic. But here is the real problem I'm having. It ignores the time stamp but doesn't group? I want to know (for example) on 2/6/12 how much money Lead Source ABC produced? And it still seperates each Job instead of adding all the Total Tickets for each Job into one group?

Ryan A Nelsch

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-08-23 : 16:29:16

As Chad mentioned, the syntax is wrong.

Also, you need to group by the same value as you are selecting:

GROUP BY  Project.LeadSource, Convert (Varchar(10), ClosedDate, 101),

Also, it is, generally, better to manipulate the date is a way that don't convert it to a string. For example:

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, SYSDATETIME()), 0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources