SQL - Conversion of char to datetime data type error

[darbid]

I could never write a query like this, I am getting help from someone with sql experience but zero Access/Sql server experience

Could someone please help us with this error message

Error - the conversion of a char data type to a datetime data type resulted in an out of range datetime value #242

We are using the below query in Access as a passthrough query

[FONT=Arial][COLOR=#0000ff][SIZE=2]select project.yemo , contactcount,  projectcount
From
(SELECT year(Date_of_Contact) + month(Date_of_Contact) AS yemo, count(Date_of_Contact) as  contactcount
From [tbl_LuT Vorgang]
WHERE  [tbl_LuT  Vorgang].Date_of_Contact >= '01/10/2008' AND [tbl_LuT  Vorgang].Date_of_Contact <= '30/03/2009'
GROUP by  year(Date_of_Contact) +  month(Date_of_Contact) )  contact
full Outer join
(SELECT  year(Date_of_Project) +  month(Date_of_Project) AS yemo,  count (Date_of_Project) as projectcount
From [tbl_LuT Vorgang]
WHERE  [tbl_LuT Vorgang].Date_of_Project >= '01/10/2008'  AND [tbl_LuT  Vorgang].Date_of_Project <='30/03/2009'
GROUP by  year(Date_of_Project) +  month(Date_of_Project) ) project [/SIZE][/COLOR][/FONT]
 [FONT=Arial][COLOR=#0000ff][SIZE=2]ON isnull(contact.yemo,'XZ1ZX') = isnull(project.yemo,'XZ1ZX');  [/SIZE][/COLOR][/FONT]

Ps: Mods I put this in the wrong forum: I hope that I deleted the other one properly

 

[Dennisk]

It may be due to the fact that for date comparison the date should be in in mm/dd/yyyy format and not dd/mm/yyyy.

Otherwise what I would do is try to determine where the error is by commenting out everything but the select statement, if it runs ok then add where clause.

Another problem may be that one of the dates is a char field containing an invalid date.

 

[DCrake]

Your actual sql syntax should be

select project.yemo , contactcount,  projectcount
From
(SELECT Year(Date_of_Contact) + Month(Date_of_Contact) AS yemo, count(Date_of_Contact) as  contactcount
From [tbl_LuT Vorgang]
WHERE  [tbl_LuT  Vorgang].Date_of_Contact >= [COLOR="Red"]#[/COLOR]01/10/2008[COLOR="red"]# [/COLOR]AND [tbl_LuT  Vorgang].Date_of_Contact <= [COLOR="red"]#[/COLOR]30/03/2009[COLOR="red"]#[/COLOR]
GROUP by  Year(Date_of_Contact) +  Month(Date_of_Contact) )  contact
full Outer join
(SELECT  Year(Date_of_Project) +  Month(Date_of_Project) AS yemo,  count (Date_of_Project) as projectcount
From [tbl_LuT Vorgang]
WHERE  [tbl_LuT Vorgang].Date_of_Project >= [COLOR="red"]#[/COLOR]01/10/2008[COLOR="red"]# [/COLOR] AND [tbl_LuT  Vorgang].Date_of_Project <=[COLOR="red"]#[/COLOR]30/03/2009[COLOR="red"]#[/COLOR]
GROUP by  Year(Date_of_Project) +  Month(Date_of_Project) )[B] project 
 [B]ON[/B] IsNull(contact.yemo,'XZ1ZX') = isnull(project.yemo,'XZ1ZX');  [/B]

Not acually sure what the bold bit of code is supposed to be doing? The main issue is that you needed #'s around your dates not single quotes.

 

[gemma-the-husky]

darbid

without checking in detail, i struggle to believe that the quoted expression (from the start of your code) below can produce the required result. (not that you have repeated this syntax several times)

Year(Date_of_Contact) + Month(Date_of_Contact) AS yemo

ie 2009 + 3 will give 2012 (won't it?)

you may mean year() & month() - although this may still not work correctly, producing

20093, instead of 200903

-----------
perhaps you could clarify exactly what you are trying to accomplish

 

[Dennisk]

DCrake

if it's a pass through query to SQL then the date delimiter will be ' and not # which is the date delimiter for the Access Jet database Engine.

 

[darbid]

Thanks guys for looking at this complicated stuff.

Yes it is a PASS Through query to SQL SERVER 2000

GTH we think you are on the right track.

We want to concatenate the year and month so that we get 200903 - this is why we are using + but this does not work at all.

Does anyone have a suggestion?

 

[Banana]

This is what I use to convert a SQL Server's date into a yyyymm format

REPLACE(CONVERT(Varchar(7), <NameOfDateColumn>, 120), '-', '')

(the number 120 provides format of yyyy-mm- if you google for SQL Server Date time, you should be able to find a list of several different supported format and how to cast one format into other).

Hope that helps.

 

[darbid]

REPLACE(CONVERT(Varchar(7), <NameOfDateColumn>, 120), '-', '')

(the number 120 provides format of yyyy-mm- if you google for SQL Server Date time, you should be able to find a list of several different supported format and how to cast one format into other). Hope that helps.

Certainly did, thanks heaps, it got us searching for the right way of doing this in SQL Server.

The solution is way beyond me. In our opinion both SQL Server and Jet give very unhelpful error messages. But if you are here because you have an error message similar to the topic of this thread, it appears that it was being caused by one of my test dates being '31/03/2009' because as soon as we changed this to '03/03/2009' that error message was gone. The query and result is way more complicated then that so I cannot say that it was 100% the reason.

select project.yemo, contactcount,  projectcount
From
(SELECT  convert(varchar(7), Date_of_Contact, 120)  as yemo, count(Date_of_Contact) as  contactcount
From [tbl_LuT Vorgang]
WHERE  Date_of_Contact >= '01/10/2008' AND Date_of_Contact <= '03/03/2009'
GROUP by convert(varchar(7), Date_of_Contact, 120)  )  contact
full Outer join
(SELECT  convert(varchar(7), Date_of_Project, 120) as yemo ,  count (Date_of_Project) as projectcount
From [tbl_LuT Vorgang]
WHERE Date_of_Project >= '01/10/2008'  AND Date_of_Project <='03/03/2009'
GROUP by convert(varchar(7), Date_of_Project, 120) ) project 
 ON contact.yemo = project.yemo;

Thank you again for everyone helping.

 

[Banana]

Just a FYI-

You can (and should) use BETWEEN instead:

WHERE Date_of_Contact BETWEEN '01/10/2008' AND '03/03/2009'

(supported by both Jet and SS.)