DateDiff not working

got an error conversion failed from character string

Page 1 of 1

10 Replies - 3005 Views - Last Post: 27 October 2009 - 11:42 AM Rate Topic: -----

#1 micke  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 56
  • Joined: 25-August 08

DateDiff not working

Post icon  Posted 22 October 2009 - 04:10 AM

Hi,

I'm trying to do an DateDiff with two dates

the first date is 2009-10-22 10:00:00 (order_main.deliveryDate - datetime) and the second date is oct 22 2009 10:00AM (log_items.item - varchar)

(SELECT DATEDIFF(dayofyear,(SELECT CAST(SUBSTRING(dbo.log_items.item, 8, 4) + '-' + SUBSTRING(dbo.log_items.item, 1, 3) + '-' + SUBSTRING(dbo.log_items.item, 5, 2) AS varchar)), order_main.deliveryDate)) AS Diff


but i got the error :
Conversion failed when converting date and/or time from character string.

i have also tried:

(SELECT CONVERT(DateTime,'Sep 18 2009 2:41PM',103))
(SELECT CONVERT(DateTime,'dbo.log_items.item',103))




SELECT cm.customerName, log_items_3.item AS Ordernr, order_main.customer_orderno, 
				order_main.plannedDeliveryDate, order_main.deliveryDate, dbo.log_items.item AS Date,

FROM dbo.log_main 

INNER JOIN dbo.log_items ON dbo.log_main.logId = dbo.log_items.fk_logId 
INNER JOIN dbo.log_items AS log_items_2 ON dbo.log_main.logId = log_items_2.fk_logId 
INNER JOIN dbo.log_items AS log_items_3 ON dbo.log_main.logId = log_items_3.fk_logId 
INNER JOIN dbo.log_items AS log_items_4 ON dbo.log_main.logId = log_items_4.fk_logId 
INNER JOIN dbo.description_orderstatus ON log_items_2.item = dbo.description_orderstatus.id 
INNER JOIN dbo.description_orderstatus AS description_orderstatus_1 ON log_items_4.item = description_orderstatus_1.id 
INNER JOIN dbo.order_main ON log_items_3.item = dbo.order_main.orderId
INNER JOIN customers_main cm ON order_main.fk_customerId = cm.customerId 
WHERE (dbo.log_main.fk_logTypeId = 2) AND (dbo.log_items.fk_logItemTypeId = 1) AND (log_items_2.fk_logItemTypeId = 9) AND 
	  (log_items_3.fk_logItemTypeId = 8) AND (log_items_4.fk_logItemTypeId = 10) AND cm.customerId = 822 
	   AND description_orderstatus_1.name ='Levererad' AND order_main.deliveryDate <> 0 
ORDER BY dbo.order_main.orderId desc


Any suggestions?

Edit: If i run the SQL like 2-3 times, then it works.. this seems to happen every time over 65 lines using TOP 65, but sometimes even TOP 200 works...

This post has been edited by micke: 22 October 2009 - 04:29 AM


Is This A Good Question/Topic? 0
  • +

Replies To: DateDiff not working

#2 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: DateDiff not working

Posted 22 October 2009 - 12:50 PM

What database app are you using? I tried CDate function on both of your date examples and they both converted to the form: 10/22/2009 10:00:00 AM. Although should not be needed on the first as it is a date type and my test without CDate still processed variable set to that value as if it were in the above form.

This post has been edited by June7: 22 October 2009 - 12:52 PM

Was This Post Helpful? 0
  • +
  • -

#3 micke  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 56
  • Joined: 25-August 08

Re: DateDiff not working

Posted 23 October 2009 - 11:13 AM

Ms Sql 2008.

it seems as an problem when i try to convert this oct 22 2009 10:00AM (string) to an date and then do an datediff...

and it also seems to stop around 65 rows, 1 time it managed 100 rows (of total around 12000)
Was This Post Helpful? 0
  • +
  • -

#4 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: DateDiff not working

Posted 23 October 2009 - 03:25 PM

Looks your interval designation (dayofyear) may be wrong.

This: DateDiff("d", "oct 12 2009 10:00:00AM", #10/22/2009 10:00:00#)
Results in: 12

When I set variables:
x = "oct 12 2009 10:00:00AM"
y = #10/22/2009 10:00:00#
This: DateDiff("d", x, y)
Also results in: 12

Try:
DateDiff("d", ... As varchar, order_main.deliveryDate)

Also, looks you want to concatenate values in the SELECT statement for the date info? For concatenation should use the ampersand (&) instead of plus (+). Need date type delimiter. Try (I might not have the parenthesis right, is this part of an even more complex query?):
"(SELECT DATEDIFF('d', (SELECT #" & SUBSTRING(dbo.log_items.item, 8, 4) & "-" & SUBSTRING(dbo.log_items.item, 1, 3) & "-" & SUBSTRING(dbo.log_items.item, 5, 2) & "# AS varchar)" & order_main.deliveryDate & ")) AS Diff"

This post has been edited by June7: 23 October 2009 - 04:03 PM

Was This Post Helpful? 0
  • +
  • -

#5 micke  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 56
  • Joined: 25-August 08

Re: DateDiff not working

Posted 26 October 2009 - 03:58 AM

Hi, thanks...

But i still got the same error even if i use & instead of an + and same using 'd" instead of dayofyear.

no the hole sql is included.
Was This Post Helpful? 0
  • +
  • -

#6 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5801
  • View blog
  • Posts: 12,638
  • Joined: 16-October 07

Re: DateDiff not working

Posted 26 October 2009 - 04:53 AM

View Postmicke, on 22 Oct, 2009 - 05:10 AM, said:

the first date is 2009-10-22 10:00:00 (order_main.deliveryDate - datetime) and the second date is oct 22 2009 10:00AM (log_items.item - varchar)

??? Your dates are identical. The result would be 0 no matter what you do, right?

You, um, can't do a datediff on something that isn't a datetime. You seem to want to compare varchar. Compare things that are datetimes.

I'm not even sure if you're looking to use datediff so much as to determine equality.

Play arround with actual dates to see how the function works

e.g.
select cast('2009-10-22 10:00:00' as datetime), cast('Sep 18 2009 2:41PM' as datetime),
	GetDate(), 
	DateDiff(day, cast('2009-10-22 10:00:00' as datetime), GetDate())




Then, try it in a statement.

SELECT cm.customerName, 
		log_items_3.item AS Ordernr, 
		order_main.customer_orderno, 
		order_main.plannedDeliveryDate, 
		order_main.deliveryDate, 
		dbo.log_items.item AS [Date]
	FROM dbo.log_main 
		INNER JOIN dbo.log_items
			ON dbo.log_main.logId = dbo.log_items.fk_logId 
				AND dbo.log_items.fk_logItemTypeId = 1
		INNER JOIN dbo.log_items AS log_items_2 
			ON dbo.log_main.logId = log_items_2.fk_logId 
				AND log_items_2.fk_logItemTypeId = 9
		INNER JOIN dbo.log_items AS log_items_3 
			ON dbo.log_main.logId = log_items_3.fk_logId 
				AND log_items_3.fk_logItemTypeId = 8
		INNER JOIN dbo.log_items AS log_items_4 
			ON dbo.log_main.logId = log_items_4.fk_logId 
				AND log_items_4.fk_logItemTypeId = 10
		INNER JOIN dbo.description_orderstatus 
			ON log_items_2.item = dbo.description_orderstatus.id 
		INNER JOIN dbo.description_orderstatus AS description_orderstatus_1 
			ON log_items_4.item = description_orderstatus_1.id 
				AND description_orderstatus_1.name ='Levererad' 
		INNER JOIN dbo.order_main 
			ON log_items_3.item = dbo.order_main.orderId
				AND order_main.deliveryDate <> 0 
		INNER JOIN customers_main cm 
			ON order_main.fk_customerId = cm.customerId 
				AND cm.customerId = 822 
	WHERE dbo.log_main.fk_logTypeId = 2
		AND cast(dbo.log_items.item as datetime) > cast('Sep 18 2009 2:41PM' as datetime)
	ORDER BY dbo.order_main.orderId desc



Hope this helps.
Was This Post Helpful? 0
  • +
  • -

#7 micke  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 56
  • Joined: 25-August 08

Re: DateDiff not working

Posted 26 October 2009 - 07:25 AM

thanks will try!

Yes the dates is the same and should result in 0, but other dates in the table dosen't i only took thoose as an example.
Was This Post Helpful? 0
  • +
  • -

#8 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: DateDiff not working

Posted 26 October 2009 - 02:02 PM

No longer lets me edit so can't correct typo in my last post. The results of the DateDiff calcs is 10 not 12.
Was This Post Helpful? 0
  • +
  • -

#9 micke  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 56
  • Joined: 25-August 08

Re: DateDiff not working

Posted 27 October 2009 - 05:16 AM

View Postbaavgai, on 26 Oct, 2009 - 03:53 AM, said:

View Postmicke, on 22 Oct, 2009 - 05:10 AM, said:

the first date is 2009-10-22 10:00:00 (order_main.deliveryDate - datetime) and the second date is oct 22 2009 10:00AM (log_items.item - varchar)

??? Your dates are identical. The result would be 0 no matter what you do, right?

You, um, can't do a datediff on something that isn't a datetime. You seem to want to compare varchar. Compare things that are datetimes.

I'm not even sure if you're looking to use datediff so much as to determine equality.

Play arround with actual dates to see how the function works

e.g.
select cast('2009-10-22 10:00:00' as datetime), cast('Sep 18 2009 2:41PM' as datetime),
	GetDate(), 
	DateDiff(day, cast('2009-10-22 10:00:00' as datetime), GetDate())




Then, try it in a statement.

SELECT cm.customerName, 
		log_items_3.item AS Ordernr, 
		order_main.customer_orderno, 
		order_main.plannedDeliveryDate, 
		order_main.deliveryDate, 
		dbo.log_items.item AS [Date]
	FROM dbo.log_main 
		INNER JOIN dbo.log_items
			ON dbo.log_main.logId = dbo.log_items.fk_logId 
				AND dbo.log_items.fk_logItemTypeId = 1
		INNER JOIN dbo.log_items AS log_items_2 
			ON dbo.log_main.logId = log_items_2.fk_logId 
				AND log_items_2.fk_logItemTypeId = 9
		INNER JOIN dbo.log_items AS log_items_3 
			ON dbo.log_main.logId = log_items_3.fk_logId 
				AND log_items_3.fk_logItemTypeId = 8
		INNER JOIN dbo.log_items AS log_items_4 
			ON dbo.log_main.logId = log_items_4.fk_logId 
				AND log_items_4.fk_logItemTypeId = 10
		INNER JOIN dbo.description_orderstatus 
			ON log_items_2.item = dbo.description_orderstatus.id 
		INNER JOIN dbo.description_orderstatus AS description_orderstatus_1 
			ON log_items_4.item = description_orderstatus_1.id 
				AND description_orderstatus_1.name ='Levererad' 
		INNER JOIN dbo.order_main 
			ON log_items_3.item = dbo.order_main.orderId
				AND order_main.deliveryDate <> 0 
		INNER JOIN customers_main cm 
			ON order_main.fk_customerId = cm.customerId 
				AND cm.customerId = 822 
	WHERE dbo.log_main.fk_logTypeId = 2
		AND cast(dbo.log_items.item as datetime) > cast('Sep 18 2009 2:41PM' as datetime)
	ORDER BY dbo.order_main.orderId desc



Hope this helps.






Still got the error : Conversion failed when converting date and/or time from character string. when i try your suggestions. Problem is that log_items.item is an varchar and not and datetime... This is an varchar becauce it contains more info then the date, but the sql above only takes out the date....

but i would like to return it as an datetime, since then the report server generats an excel fil, and you can't do nothing with this cell in excel since it is an text...
Was This Post Helpful? 0
  • +
  • -

#10 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: DateDiff not working

Posted 27 October 2009 - 11:07 AM

Might need the date delimiter in the Where clause:

AND #cast(dbo.log_items.item as datetime)# > #cast('Sep 18 2009 2:41PM' as datetime)#

Also, if you build the SQL string in stages, would be easier to debug.

What is dbo? Is it a variable? If it is, how can the SQL know its value when you have it in the string as literal text?
Was This Post Helpful? 0
  • +
  • -

#11 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5801
  • View blog
  • Posts: 12,638
  • Joined: 16-October 07

Re: DateDiff not working

Posted 27 October 2009 - 11:42 AM

If you could show some examples of the text you're trying to cast as datetime, that might help.

View PostJune7, on 27 Oct, 2009 - 12:07 PM, said:

AND #cast(dbo.log_items.item as datetime)# > #cast('Sep 18 2009 2:41PM' as datetime)#


The wonky pound syntax only works in Access. I don't know for sure that cast does. It think it's something like "CDate" or "#12/21/2012#", both of which also only work in Access.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1