CONVERT and syntax - 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

CONVERT and syntax

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-01-22 : 09:20:12

Here's my sp

SELECT DATEADD(day, DATEDIFF(day, 0, LastDateIn), 0) AS Date_Checked_In, COUNT(*) AS No_Files
FROM tblFiles
WHERE (CONVERT(datetime, LastDateIn, 103) >= 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

I want Date_Checked_In not to show the time. I have tried using convert on the 1st line to just show the date but keep getting a syntax error.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-22 : 09:25:38

use

dateadd(day, datediff(day, 0, Date_Checked_In), 0)

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-01-22 : 09:27:55

Do you mean

SELECT DATEADD(day, DATEDIFF(day, 0, LastDateIn), 0) AS dateadd(day, datediff(day, 0, Date_Checked_In), 0), COUNT(*) AS No_Files

as I get an error doing that

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-22 : 09:34:55

[code]Select Convert(varchar(10), LastDateIn, 101) AS Date_Checked_In,
....
FROM tblFiles
WHERE (CONVERT(datetime, LastDateIn, 103) >= DATEADD(day, DATEDIFF(day, 10, GETDATE()), 0))
GROUP BY Convert(varchar(10), LastDateIn, 101)
ORDER BY 1 DESC
[/code]

The error you must be getting because the GROUP BY expression does not match with SELECT list.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-22 : 09:34:59

Sorry. i mean


SELECT DATEADD(day, DATEDIFF(day, 0, LastDateIn), 0) AS Date_Checked_In, COUNT(*) AS No_Files
FROM   tblFiles
WHERE  LastDateIn >= DATEADD(day, DATEDIFF(day, 10, GETDATE()), 0) -- 10 days ago ?
GROUP BY DATEADD(day, DATEDIFF(day, 0, LastDateIn), 0)
ORDER BY Date_Checked_In DESC

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-01-22 : 09:41:31

Thank you both - it is working perfectly now

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources