SQL Select and date problem

[nka]

hello,

I have a table with these columns:

IDsales, salesDate, salesmanID, itemDesc

I need an SQL querry to select all the sales from a certain date, with the summed sales groupped by salesmanID... The problem with the querry is that the data in the salesDate field is in a long format (with dd/mm/yyyy hh:mm:sss) and I dont know how to set the querry to disregard the "time" info, and take in account the "date" part only.

I am just a novice in SQL - all help is welcome )

[dglienna]

SQL takes care of that automatically. It's stored as a DATA field, which also has a time stamp.

[Alsvha]

hello,

I have a table with these columns:

IDsales, salesDate, salesmanID, itemDesc

I need an SQL querry to select all the sales from a certain date, with the summed sales groupped by salesmanID... The problem with the querry is that the data in the salesDate field is in a long format (with dd/mm/yyyy hh:mm:sss) and I dont know how to set the querry to disregard the "time" info, and take in account the "date" part only.

I am just a novice in SQL - all help is welcome )

You do not mention which database you use, but if you use SQL Server there's a number of ways to do that.

If you only want those on a specific date, you can either make your query take larger then midnight and less then midnight.
Such as
myDateField > '2009-12-24' and myDateField < '2009-12-25' which should net you all days on the 24th of December.

You can change the query to use the DATEPART function to clean your date time for the time portion.
http://msdn.microsoft.com/en-us/libr...5(SQL.80).aspx

You can use the mathematical representation of the datetime to do the same (a bit more confusing to read)
CAST(FLOOR(CAST(myDateField AS FLOAT)) AS DATETIME)
which also returns the datetime cleaned for the time portion.

And so on ....

[nka]

Thanks for your help!

I am using an Miscrosoft Access database, and here is my SQL Select command:

SELECT salesDate, salesmanID, SUM(pricePaid) AS Expr1, itemDesc, IDSales
FROM Sales
GROUP BY salesDate, salesmanID, IDSales
HAVING (salesmanID = ?)
ORDER BY salesDate DESC

but it returns no results? what am I missing?

[dglienna]

I'd guess it's this line:

HAVING (salesmanID = ?)

please use code tags in the future

[Alsvha]

I agree with dglienna - my guess would be the having clause.
What should be the purpose of the having clause in your query?