Using DateAdd and Convert - 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 DateAdd and Convert

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-01-18 : 10:46:26

Here's my sp which doesn't do what I want

I want it to only list records with LastDateIn 10 days prior to today's date.

CREATE Procedure [dbo].[spRMU_NoFilesBookedInByDay]
AS

SELECT Convert(nvarchar, LastDateIn,103) AS Expr1, COUNT(Status) AS Expr2
FROM tblFiles
where Convert(datetime, LastDateIn,103) < dateadd(day, -10, getdate())
GROUP BY Convert(nvarchar, LastDateIn,103)
ORDER BY Convert(nvarchar, LastDateIn,103) DESC

GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-18 : 10:52:07

select dateadd(day, datediff(day, 0, getdate()), 0), count(*) from tblFiles
where lastdatein < dateadd(day, datediff(day, 10, getdate()), 0)
group by dateadd(day, datediff(day, 0, getdate()), 0)

Peter Larsson
Helsingborg, Sweden

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-01-18 : 11:01:55

I am getting the error below when I check the syntax of the sp

Error 164 : GROUP BY expressions must refer to column names that appear in the select list.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-18 : 11:09:45

My bad

select dateadd(day, datediff(day, 0, lastdatein), 0), count(*) from tblFiles
where lastdatein < dateadd(day, datediff(day, 10, getdate()), 0)
group by dateadd(day, datediff(day, 0, lastdatein), 0)

Peter Larsson
Helsingborg, Sweden

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-01-18 : 11:33:22

Thank you. I have amended your sql a bit as I what I wanted was the last 10 days from now - sorry I did not explain myself properly.

This is what I have now and it works fine.

CREATE Procedure [dbo].[spRMU_NoFilesBookedInByDay]
AS

select dateadd(day, datediff(day, 0, lastdatein), 0), count(*) from tblFiles
where lastdatein > dateadd(day, datediff(day, 10, getdate()), 0)
group by dateadd(day, datediff(day, 0, lastdatein), 0)
order by dateadd(day, datediff(day, 0, lastdatein), 0) desc

GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-18 : 11:49:20

>= will the best choice.

Thank you.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources