converting datetime from character string

Dev Shed Forums Sponsor:

December 2nd, 2004, 05:25 AM

juecal

Registered User

Join Date: Apr 2004

Location: California

Posts: 27

Time spent in forums: < 1 sec
Reputation Power: 0

converting datetime from character string

Can anyone tell me what this error message means and how I can correct it on my statement:

Code:

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

Here's my query statement:

Code:

SELECT animalid AS "Animal ID", 
name AS "Name", categoryid AS "Category ID", 
DATENAME(MONTH, dateBorn) + ' ' + 
DATENAME(DAY, dateBorn) + ', ' + 
DATENAME(YEAR, dateBorn) AS "Date of Birth"
FROM animal
WHERE categoryid = 'Cat' AND dateBorn = 'May%'

I know it has something to do with: dateBorn = 'May%' because
when I took out

Quote:

AND dateBorn = 'May%'

I get results, but I need to narrow it down to list the cats born in May.

December 2nd, 2004, 06:29 AM

swampBoogie

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Jan 2003

Location: Paris Uppland

Posts: 1,784

Time spent in forums: 2 Weeks 4 Days 21 h 42 m 49 sec
Reputation Power: 37

Code:

AND DATENAME(MONTH, dateBorn) = 'May'

December 3rd, 2004, 05:26 AM

juecal

Registered User

Join Date: Apr 2004

Location: California

Posts: 27

Time spent in forums: < 1 sec
Reputation Power: 0

Quote:

Originally Posted by swampBoogie

Code:

AND DATENAME(MONTH, dateBorn) = 'May'

Thanks...that worked...now i have a similar problem. Here's my statement:

Code:

 
SELECT customerid AS "Customer ID", 
lastname AS "Last Name", 
phone AS "Phone", 
DATENAME(MONTH, saleDate) + ' ' + 
DATENAME(DAY, saleDate) + ', ' + 
DATENAME(YEAR, saleDate) AS "Sale Date"
FROM Customer, Sale
WHERE saleDate 
	BETWEEN (DATENAME(MONTH, saleDate) = 'June'
	 DATENAME(DAY, saleDate) = '1') 
	AND	(DATENAME(MONTH, saleDate) = 'June'
	 DATENAME(DAY, saleDate) ='7')

The error message is: Line 9: Incorrect syntax near '='. I followed the same format as my first query. The error is on the WHERE line:

Code:

WHERE saleDate 
	BETWEEN (DATENAME(MONTH, saleDate) = 'June'
	 DATENAME(DAY, saleDate) ='1') 
	AND	(DATENAME(MONTH, saleDate) = 'June'
	 DATENAME(DAY, saleDate) ='7')

I only need the list of customers who shopped at the store the first seven days in June. Please let me know how I could correct the problem?

December 3rd, 2004, 06:31 AM

swampBoogie

Contributing User

Join Date: Jan 2003

Location: Paris Uppland

Posts: 1,784

Time spent in forums: 2 Weeks 4 Days 21 h 42 m 49 sec
Reputation Power: 37

What data do you want? From june 1st to june 7th or what?

Code:

SELECT customerid AS "Customer ID", 
lastname AS "Last Name", 
phone AS "Phone", 
DATENAME(MONTH, saleDate) + ' ' + 
DATENAME(DAY, saleDate) + ', ' + 
DATENAME(YEAR, saleDate) AS "Sale Date"
FROM Customer, Sale
WHERE month(saleDate) = 6
and day(saledate) between 1 and 7

December 3rd, 2004, 06:35 AM

swampBoogie

Contributing User

Join Date: Jan 2003

Location: Paris Uppland

Posts: 1,784

Time spent in forums: 2 Weeks 4 Days 21 h 42 m 49 sec
Reputation Power: 37

I see that you are missing the join criteria as well. What is the relation between customer and sale?

December 3rd, 2004, 06:52 AM

juecal

Registered User

Join Date: Apr 2004

Location: California

Posts: 27

Time spent in forums: < 1 sec
Reputation Power: 0

[QUOTE=swampBoogie]What data do you want? From june 1st to june 7th or what?

Yes, June 1 - 7.
I updated my query with the corrections that you made and got this error: Ambiguous column name 'customerId'.
It seems that customerId both appears on Customer & Sale tables. So, I declared customerId, but here's another error that I get: Line 9: Incorrect syntax near 'MONTH'. Am I declaring customerId incorrectly here:

Code:

 
SELECT c.customerId AS "Customer ID", 
lastName AS "Last Name", 
phone AS "Phone", 
DATENAME(MONTH, saleDate) + ' ' + 
DATENAME(DAY, saleDate) + ', ' + 
DATENAME(YEAR, saleDate) AS "Sale Date"
FROM Customer AS c, Sale AS s 
WHERE (c.customerId = s.customerId)
MONTH(saleDate) = 6
AND DAY(saleDate) BETWEEN 1 AND 7

December 3rd, 2004, 07:21 AM

juecal

Registered User

Join Date: Apr 2004

Location: California

Posts: 27

Time spent in forums: < 1 sec
Reputation Power: 0

Quote:

Originally Posted by swampBoogie

I see that you are missing the join criteria as well. What is the relation between customer and sale?

Both has customerId. Hence, they are joint through customerId. customerId is the PK for Customer and therefor it's a FK for Sale.
Anyway, I think I figured out how to solve the problem that I just addressed earlier. Here's my updated query statements:

Code:

 
SELECT c.customerId AS "Customer ID", 
 lastName AS "Last Name", 
 phone AS "Phone", 
 DATENAME(MONTH, saleDate) + ' ' + 
 DATENAME(DAY, saleDate) + ', ' + 
 DATENAME(YEAR, saleDate) AS "Sale Date"
   FROM Customer AS c, Sale AS s 
   WHERE (c.customerId = s.customerId)
 AND MONTH(saleDate) = 6
 AND DAY(saleDate) BETWEEN 1 AND 7
   ORDER BY saleDate

By running this query, I was able to get the results that I needed. I just added the ORDER BY statement so the date is sorted in ascending order

THANK YOU SO MUCH for your HELP

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: