SQL DATE issues - 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

SQL Server 2005 Forums

Transact-SQL (2005)

SQL DATE issues

Author

Topic

tonybury
Starting Member

5 Posts

Posted - 2007-08-09 : 14:20:41

I am executing this against a Visual Foxpro 8.0 database. In the table the field is listed as a char. I cannot modify the table.

The problem I am having is the dateship field. When I run they query I am getting data back for each date within the range but from all years, not just the 1 year I want. I have been trying to do cast or convert functions and I just get sytax errors. Please help, my head cannot take any more banging. The query is below.

Query

SELECT DISTINCT invoice.sono, Left(invoice.custcorp,41) AS custcorp, MID(invoice.custcorp,41,213) AS billto, invoice.itemno, invoice.partno, invoice.accountno, invoice.orqtyreq, invoice.trackingno, invoice.dateship, invoice.qtyship, invoice.backorder, invoice.invoiceno, sales.orderdate, sales.custpono, Trim(employee.firstname) + Chr(43) + Trim(employee.lastname) AS employee_name
FROM employee INNER JOIN (invoice INNER JOIN sales ON invoice.sono = sales.sono) ON employee.empid = invoice.salesman
WHERE invoice.saletype <> 'OW' AND invoice.division = '0200' AND invoice.dateship >= '07/01/2007' AND invoice.dateship <= '07/30/2007'
ORDER BY invoice.dateship;

Thanx,

Tony

tonybury
Starting Member

5 Posts

Posted - 2007-08-09 : 14:22:06

I forgot to mention that the dateship field is a char(10) and the data looks like xx/xx/xxxx

Thanx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-09 : 14:36:35

Care to show us sample data and the expected result set? It's hard to figure out what your issue is without seeing data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

tonybury
Starting Member

5 Posts

Posted - 2007-08-09 : 14:52:52

As for the data all the dates are in the following format xx/xx/xxxx. This is exactly how it looks in the field 07/02/2007

The expected result set is for a report that is showing the sales orders shipped between the two listed dates which is a visual basic variable. When I retrieve the data from the variable it looks just like the above data in the field.
If I run a query for a date range or single date as above 07/02/2007, I want to see just the order shipped within that range or on that specific date. What is happening is the returned data is listing all orders shipped on 07/02/2007 and 07/02/2006, etc.... I am seeing all the previous years in the report for some reason, although I did not include those years in the query.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-09 : 15:03:12

SELECT DISTINCT invoice.sono, Left(invoice.custcorp,41) AS custcorp, MID(invoice.custcorp,41,213) AS billto, invoice.itemno, invoice.partno, invoice.accountno, invoice.orqtyreq, invoice.trackingno, invoice.dateship, invoice.qtyship, invoice.backorder, invoice.invoiceno, sales.orderdate, sales.custpono, Trim(employee.firstname) + Chr(43) + Trim(employee.lastname) AS employee_name
FROM employee INNER JOIN (invoice INNER JOIN sales ON invoice.sono = sales.sono) ON employee.empid = invoice.salesman
WHERE invoice.saletype <> 'OW' AND invoice.division = '0200' AND CAST(invoice.dateship AS DATETIME) >= '07/01/2007' AND CAST(invoice.dateship AS DATETIME) < '07/31/2007'
ORDER BY CAST(invoice.dateship AS DATETIME);

E 12°55'05.25"
N 56°04'39.16"

tonybury
Starting Member

5 Posts

Posted - 2007-08-09 : 15:10:18

Peso,

Thanks for the suggestion. That is what I had been trying and I keep getting a syntax error when I go to process the query. I also tried using convert(datetime,invoice.dateship,101) and that gives me a sytax error also. That is why I am getting frustrated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-09 : 15:19:45

Add a

SET DATEFORMAT MDY

at the top of the statement and also CAST the hardcoded values as datetime.

E 12°55'05.25"
N 56°04'39.16"

tonybury
Starting Member

5 Posts

Posted - 2007-08-09 : 15:26:52

That did the trick!! Thank you!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-09 : 16:22:11