Databases

Welcome to Dream.In.Code
	Become an Expert! Join 307,153 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 1,678 people online right now. Registration is fast and FREE... Join Now! Chat LIVE With a Expert

DateDiff not working

DateDiff not working, got an error conversion failed from character string

Options

micke	22 Oct, 2009 - 03:10 AM Post #1
New D.I.C Head Joined: 25 Aug, 2008 Posts: 48 My Contributions	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) CODE (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: CODE (SELECT CONVERT(DateTime,'Sep 18 2009 2:41PM',103)) (SELECT CONVERT(DateTime,'dbo.log_items.item',103)) CODE 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 Oct, 2009 - 03:29 AM
	This Post Was Helpful!

Register for Free to Make These Ads Go Away!

June7	RE: DateDiff Not Working 22 Oct, 2009 - 11:50 AM Post #2
D.I.C Regular Joined: 9 Dec, 2008 Posts: 485 Thanked: 38 times My Contributions	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 Oct, 2009 - 11:52 AM
	This Post Was Helpful!

micke	RE: DateDiff Not Working 23 Oct, 2009 - 10:13 AM Post #3
New D.I.C Head Joined: 25 Aug, 2008 Posts: 48 My Contributions	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)
	This Post Was Helpful!

June7	RE: DateDiff Not Working 23 Oct, 2009 - 02:25 PM Post #4
D.I.C Regular Joined: 9 Dec, 2008 Posts: 485 Thanked: 38 times My Contributions	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 Oct, 2009 - 03:03 PM
	This Post Was Helpful!

micke	RE: DateDiff Not Working 26 Oct, 2009 - 02:58 AM Post #5
New D.I.C Head Joined: 25 Aug, 2008 Posts: 48 My Contributions	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.
	This Post Was Helpful!

baavgai

RE: DateDiff Not Working

26 Oct, 2009 - 03:53 AM

Post #6

Dreaming Coder

Joined: 16 Oct, 2007
Posts: 4,351

Thanked: 412 times
Dream Kudos: 550
Expert In: C, C++, Java, C#, ASP.NET, PHP, Perl, Python, Oracle, SQL Server, MySql, HTML, JavaScript, Lua, Cheese

My Contributions

QUOTE(micke @ 22 Oct, 2009 - 05:10 AM)

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.

sql


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.

sql


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.

This Post Was Helpful!

micke	RE: DateDiff Not Working 26 Oct, 2009 - 06:25 AM Post #7
New D.I.C Head Joined: 25 Aug, 2008 Posts: 48 My Contributions	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.
	This Post Was Helpful!

June7	RE: DateDiff Not Working 26 Oct, 2009 - 01:02 PM Post #8
D.I.C Regular Joined: 9 Dec, 2008 Posts: 485 Thanked: 38 times My Contributions	No longer lets me edit so can't correct typo in my last post. The results of the DateDiff calcs is 10 not 12.
	This Post Was Helpful!

micke

RE: DateDiff Not Working

27 Oct, 2009 - 04:16 AM

Post #9

New D.I.C Head

Joined: 25 Aug, 2008
Posts: 48

My Contributions

QUOTE(baavgai @ 26 Oct, 2009 - 03:53 AM)

QUOTE(micke @ 22 Oct, 2009 - 05:10 AM)

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)

sql


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.

sql


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...

This Post Was Helpful!

June7	RE: DateDiff Not Working 27 Oct, 2009 - 10:07 AM Post #10
D.I.C Regular Joined: 9 Dec, 2008 Posts: 485 Thanked: 38 times My Contributions	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?
	This Post Was Helpful!

baavgai	RE: DateDiff Not Working 27 Oct, 2009 - 10:42 AM Post #11
Dreaming Coder Joined: 16 Oct, 2007 Posts: 4,351 Thanked: 412 times Dream Kudos: 550 Expert In: C, C++, Java, C#, ASP.NET, PHP, Perl, Python, Oracle, SQL Server, MySql, HTML, JavaScript, Lua, Cheese My Contributions	If you could show some examples of the text you're trying to cast as datetime, that might help. QUOTE(June7 @ 27 Oct, 2009 - 12:07 PM) 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.
	This Post Was Helpful!