• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2102
  • Last Modified:

incorrect syntax near 00

hi
anyone know why this wont work?
'checkeddate' is a date which has data like '28/04/2006' or '28/04/2006 11:11:11'

SELECT     didreply, COUNT(didreply) AS didreplynum
FROM         websitestatistics
WHERE     (checkeddate BETWEEN 28 / 04 / 2006 00 : 00 : 00 AND 28 / 04 / 2006 23 : 59 : 59)
GROUP BY didreply;

thanks
0
amoran
Asked:
amoran
  • 7
  • 4
  • 3
  • +1
1 Solution
 
MikeWalshCommented:
Put quotes around your dates (single quotes)
0
 
MikeWalshCommented:
also your spaces are causing an issue...

look up datetime in books online.
0
 
amoranAuthor Commented:
now

SELECT     didreply, COUNT(didreply) AS didreplynum
FROM         websitestatistics
WHERE     (checkeddate BETWEEN '28/04/2006 00:00:00' AND '28/04/2006 23:59:59')
GROUP BY didreply;

'the conversion of a char data type to a datetime datatype resulted in an out of range datetime value'
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MikeWalshCommented:
between '28/04/2006 00:00:00' AND '28/04/2006 23:59:59'

assuming your regional settings are set to understand the date.
0
 
Brian CroweDatabase AdministratorCommented:
You would be better off putting you dates in "yyyymmdd HH:MM:ss" format to avoid any regional settings but Mike is correct you need single quotes around your dates.  You also appear to have a lot of extra white space which might be throwing it off.


SELECT     didreply, COUNT(didreply) AS didreplynum
FROM         websitestatistics
WHERE     (checkeddate BETWEEN '20060428 00:00:00' AND '20060428 23:59:59')
GROUP BY didreply
0
 
MikeWalshCommented:
Try using something like this:

'04/28/2006 00:00:00.000' AND '04/28/2006 23:59:59.999'

Your regional settings may not like the month first. This is a dateformat issue. You could change your date format first.. I personally prefer using my dates like this '20060428'
0
 
MikeWalshCommented:
I wish you could see that someone is already replying :-) Bri is correct, that is what I typed above. Bri's comment about the yyyymmdd hh:mm:ss is dead on.

You use that format in SQL, and you are guaranteed to work no matter if your connected to an installation in Sub Saharan Africa, Siberia, USA, UK or Iceland.
0
 
Atlanta_MikeCommented:
SELECT     didreply, COUNT(didreply) AS didreplynum
FROM         websitestatistics
WHERE    convert(varchar(15),checkeddate,103)  = '28/04/2006'
GROUP BY didreply;
0
 
Brian CroweDatabase AdministratorCommented:
It's like a shark frenzy around here...all this activity for a 20-pt question LoL

I think it's more important to us than to him.
0
 
amoranAuthor Commented:
BriCrowe that seemed to work alright.

The problem I have now is - my original c# code is

string sqlQuery = "SELECT didreply, COUNT(didreply) AS didreplynum FROM websitestatistics WHERE checkeddate between @selectedDate and @selectedDatePlusOneDay GROUP BY didreply";
            SqlCommand sqlCommand = new SqlCommand(sqlQuery, sqlConn);
            sqlCommand.Parameters.Add("@selectedDate", SqlDbType.DateTime);
            sqlCommand.Parameters[0].Value = monthCalendar1.SelectionStart;
            sqlCommand.Parameters.Add("@selectedDatePlusOneDay", SqlDbType.DateTime);
            sqlCommand.Parameters[1].Value = monthCalendar1.SelectionStart.AddDays(1).AddSeconds(-1);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);

Do you know how to add the two dates in the format that works?

Thanks
0
 
MikeWalshCommented:
That almost sounds like a question for the C# group. You see how it works in SQl and now you need to know how to get it to display that way.
0
 
Brian CroweDatabase AdministratorCommented:
you shouldn't have to worry about format in this case since dates dont' really have a format at this level but you do need to add the quotes as originally suggested.

string sqlQuery = "SELECT didreply, COUNT(didreply) AS didreplynum FROM websitestatistics WHERE checkeddate between '@selectedDate' and '@selectedDatePlusOneDay' GROUP BY didreply";
0
 
amoranAuthor Commented:
BriCrowe

Changed to

string sqlQuery = "SELECT didreply, COUNT(didreply) AS didreplynum FROM websitestatistics WHERE checkeddate between '@selectedDate' and '@selectedDatePlusOneDay' GROUP BY didreply";
           
but i got a crash

Syntax error converting datetime from character string.
0
 
Brian CroweDatabase AdministratorCommented:
Looking at it again...I've never had to put quotes around a parameter value when used this way.  In fact the code you posted above looks just fine.
0
 
MikeWalshCommented:
I would say accept and points to bricrowe..
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now