SQL String erroring out 1

[chuckh70]

I managed to get this working in query analyzer, but having problems using it in my page.

How would I format this properly?

SELECT     DATEADD(dd, Create_Time / 86400, '1/1/1970') AS 'Date Open', COUNT(*) AS tcktOpen
FROM         HPD_HelpDesk
WHERE     (Assigned_To_Group_ = 'Customer Service Desk') AND (Status < 6) AND (DATEADD(dd, Create_Time / 86400, '1/1/1970') >= '11/1/2004')
GROUP BY DATEADD(dd, Create_Time / 86400, '1/1/1970') 
 Order By DATEADD(dd, Create_Time / 86400, '1/1/1970')  ASC

This is my first server attempts

Dim SelectCmd As String = "select DATEADD(dd, Create_Time / 86400, '1/1/1970') AS 'Date Open', count(*) as 'Open Tickets' from HPD_HelpDesk where Assigned_To_Group_ = '" & MySelect.Value & "' and status < 4 and (DATEADD(dd, Create_Time / 86400, '1/1/1970') >= '" & unxStart & "')GROUP BY DATEADD(dd, Create_Time / 86400, '1/1/1970')Order By DATEADD(dd, Create_Time / 86400, '1/1/1970')  ASC"

 

[AtomicChip]

Three things:

1) Is there an actual error being reported? If so, what is it?
2) Copy and paste what you have in your code into the query analyzer (change the variables obviously) - there are a couple small differences in the queries (i didn't look to see if they could actually cause you problems or not
3) Is there a reason that you're creating the SQL command in code instead of using stored procedures?

-----------------------------------------------

http://www.liquidfuzion.com

"The night sky over the planet Krikkit is the least interesting sight in the entire universe."
-Hitch Hiker's Guide To The Galaxy

 

[chuckh70]

Here is the error I get, and the query runs in query analyzer.

Not using stored procedure, because I have never done it, and not sure how to utilize it.

Syntax error converting datetime from character string.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Syntax error converting datetime from character string.

 

[AtomicChip]

Try DateTime.Parse(MySelect.Value).

You should really look into using stored procedures. Some people don't agree, but once I started using them I never looked back.

http://www.developer.com/db/article.php/3438221

-----------------------------------------------

http://www.liquidfuzion.com

"The night sky over the planet Krikkit is the least interesting sight in the entire universe."
-Hitch Hiker's Guide To The Galaxy

 

[chiph]

Using 1/1/1970 style date formatting can lead to problems. While in this case, it's not an issue, if you have 1/2/2005, you can't tell if it's January 2nd or February 1st.

I would suggest that if you're passing a date as a string to a database, you should use ISO-8601 formatting: yyyy-mm-dd

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[DaZZleD]

i think your problem is here :
'" & unxStart & "'

the unxStart string might be of different format than that of a valid datetime. you might want to try using an iso format like chiph suggested or using sqlparameters for your query

--------------------------
"two wrongs don't make a right, but three lefts do" - the unknown sage

 

[chuckh70]

It was. I was calling the unix time do to some conversions I was doing. Swapped and it worked fine.

Thanks