ODBC Type mismatch error
-
I am running a SQL statement in C#, where I am using the WHERE clause to limit a query to a date. Here's the SQL:
DbCommand.CommandText = "SELECT SUM-DATE, SUM-STORE, SUM(SUM-RETURNS) FROM SUMMARY WHERE SUM-DATE = '" + dtProcDate + "' GROUP BY SUM-STORE";
The dtProcDate is assigned here:
if (DateTime.Now.DayOfWeek == DayOfWeek.Monday)
{
dtProcDate = DateTime.Now.AddDays(-2);
}
else
{
dtProcDate = DateTime.Now.AddDays(-1);
}When I query just the date, I get the date in the MM/DD/YYYY HH:MM:SS AM/PM format, which is what dtProcDate shows as it's format also. Here is the Error:
ERROR [HY000] Data Type Mismatch in WHERE clause, Value '10/24/2009 2:22:39 PM'.
What am I missing here?
Jude
Depends. The most likely pair are: 1) SUM-DATE is not a DateTime. 2) Your datetime format is not what the database is expecting. Remember that
string + DateTime + string
is an implicit
string + DateTime.ToString() + string
which means it is formated to your current default environment - in this case "dd/mm/yyyy". Try changing to
dtProcDate.ToString("yyyy-MM-dd HH:mm:ss");
and you should be fine. See here for DateTime.ToString formats[^]
No trees were harmed in the sending of this message; however, a significant number of electrons were slightly inconvenienced. This message is made of fully recyclable Zeros and Ones
-
I am running a SQL statement in C#, where I am using the WHERE clause to limit a query to a date. Here's the SQL:
DbCommand.CommandText = "SELECT SUM-DATE, SUM-STORE, SUM(SUM-RETURNS) FROM SUMMARY WHERE SUM-DATE = '" + dtProcDate + "' GROUP BY SUM-STORE";
The dtProcDate is assigned here:
if (DateTime.Now.DayOfWeek == DayOfWeek.Monday)
{
dtProcDate = DateTime.Now.AddDays(-2);
}
else
{
dtProcDate = DateTime.Now.AddDays(-1);
}When I query just the date, I get the date in the MM/DD/YYYY HH:MM:SS AM/PM format, which is what dtProcDate shows as it's format also. Here is the Error:
ERROR [HY000] Data Type Mismatch in WHERE clause, Value '10/24/2009 2:22:39 PM'.
What am I missing here?
Jude
Hi, AFAIK SQL needs Date and DateTime literals to be in a specific format, which is independent of how your system shows dates and datetimes to the human user (which gets controlled by the Regional Settings Control Panel). I used Google "SQL literal date" and found this[^] amongst many others referring to ISO 8601. :)
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
-
Let me know what database you are using?? check this : here[^]
Abhishek Sur **Don't forget to click "Good Answer" if you like this Solution.
My Latest Articles-->** Microsoft Bing MAP using Javascript
CLR objects in SQL Server 2005
Uncommon C# Keywords/xml>Thanx for the link. The database is System Z. I am no longer getting a type mismatch. I changed dtProcDate to dtProcDate.ToShortDateString(), but I am not getting the result I want.
Jude
-
Depends. The most likely pair are: 1) SUM-DATE is not a DateTime. 2) Your datetime format is not what the database is expecting. Remember that
string + DateTime + string
is an implicit
string + DateTime.ToString() + string
which means it is formated to your current default environment - in this case "dd/mm/yyyy". Try changing to
dtProcDate.ToString("yyyy-MM-dd HH:mm:ss");
and you should be fine. See here for DateTime.ToString formats[^]
No trees were harmed in the sending of this message; however, a significant number of electrons were slightly inconvenienced. This message is made of fully recyclable Zeros and Ones
Thanx, but that gives me a syntax error. When I do dtProcDate.ToShortDateString(), I do not receive an error, but I do not get the results that I need. This SQL statement worked in Access....
Jude
-
Thanx, but that gives me a syntax error. When I do dtProcDate.ToShortDateString(), I do not receive an error, but I do not get the results that I need. This SQL statement worked in Access....
Jude
SQL statements based on ToShortDateString() make absolutely no sense to me, as that method relies on the Regional Settings whereas a database should not be localized. You really should use an explicit format specification as in
ToString("yyyy-MM-dd"):)Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
-
SQL statements based on ToShortDateString() make absolutely no sense to me, as that method relies on the Regional Settings whereas a database should not be localized. You really should use an explicit format specification as in
ToString("yyyy-MM-dd"):)Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
True. I was trying it for troubleshooting. When I query just the date I get a MM/DD/YYYY HH:MM:SS AM/PM in return. When I use just the dtProcDate in a compare, I receive an error of Syntax Error: Unprocessed input. Net token '6' (which is the hour of the time). When I try to use either # or quotes around the date I receive Syntax Error: Required text missing. Next token '#'. When I try a dtProcDate.ToString("MM:dd:yyyy hh:mm tt") I receive a Syntax Error: Unprocessed input Next Token: '06' (which is the hour of the time here). I am at a loss. I have never had this much trouble comparing dates!
Jude
-
True. I was trying it for troubleshooting. When I query just the date I get a MM/DD/YYYY HH:MM:SS AM/PM in return. When I use just the dtProcDate in a compare, I receive an error of Syntax Error: Unprocessed input. Net token '6' (which is the hour of the time). When I try to use either # or quotes around the date I receive Syntax Error: Required text missing. Next token '#'. When I try a dtProcDate.ToString("MM:dd:yyyy hh:mm tt") I receive a Syntax Error: Unprocessed input Next Token: '06' (which is the hour of the time here). I am at a loss. I have never had this much trouble comparing dates!
Jude
TheJudeDude wrote:
I was trying it for troubleshooting
troubleshooting code better be correct too.
TheJudeDude wrote:
When I query just the date I get a MM/DD/YYYY HH:MM:SS AM/PM in return
No. the database returns a date, which is a struct containing some numbers. And for human consumption your PC converts that into a string, according to some rules, by default influenced by the Regional Settings. That Control Panel is exactly how the human user tells the system what he likes to use as date and time formats. An explicit formatting string allows you to specify a different format, independent of Regional Settings. SQL wants date/datetime according to ISO 8601, which means dashes in the date, colons in the time, year-month-day order, a 'T' in between, etc etc. The delimiter (quote, hash, whatever) may depend on the exact database. :)
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
-
TheJudeDude wrote:
I was trying it for troubleshooting
troubleshooting code better be correct too.
TheJudeDude wrote:
When I query just the date I get a MM/DD/YYYY HH:MM:SS AM/PM in return
No. the database returns a date, which is a struct containing some numbers. And for human consumption your PC converts that into a string, according to some rules, by default influenced by the Regional Settings. That Control Panel is exactly how the human user tells the system what he likes to use as date and time formats. An explicit formatting string allows you to specify a different format, independent of Regional Settings. SQL wants date/datetime according to ISO 8601, which means dashes in the date, colons in the time, year-month-day order, a 'T' in between, etc etc. The delimiter (quote, hash, whatever) may depend on the exact database. :)
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
Thanx for the input. Are you familiar with System Z?
Jude
-
Thanx for the input. Are you familiar with System Z?
Jude
TheJudeDude wrote:
Are you familiar with System Z?
No I'm not. Google knows about it. Seems to be an IBM thing. If this isn't all happening on a Windows PC, how does it fit the C# forum at all? :)
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
-
TheJudeDude wrote:
Are you familiar with System Z?
No I'm not. Google knows about it. Seems to be an IBM thing. If this isn't all happening on a Windows PC, how does it fit the C# forum at all? :)
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
Yes google knows about it, just thought I may ask you. All I am looking for is to make a date comparison work. The database is on a Red Hat server. I need to make reports from the data through an ODBC connection from a Windows platform. There is very little documentation from the provider and the support is spotty. The company I work for just changed our POS software and I am trying to put out the same reports that management are used to having. Well, thanks for your input so far.
Jude
-
Yes google knows about it, just thought I may ask you. All I am looking for is to make a date comparison work. The database is on a Red Hat server. I need to make reports from the data through an ODBC connection from a Windows platform. There is very little documentation from the provider and the support is spotty. The company I work for just changed our POS software and I am trying to put out the same reports that management are used to having. Well, thanks for your input so far.
Jude
if all documentation were missing, I would try a few experiments on a DATE field:
SELECT * FROM tablename WHERE dateFieldName='2009-10-27'
SELECT * FROM tablename WHERE dateFieldName=#2009-10-27#
SELECT * FROM tablename WHERE dateFieldName=`2009-10-27`or on a DATETIME field, same tests however replace = by >= all this assumes a new record got added with dateFieldName set to NOW() or CURDATE() or whatever yields the current date or datetime. And of course my entire test would sit in a try-catch with the catch displaying exception.ToString() :)
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
-
if all documentation were missing, I would try a few experiments on a DATE field:
SELECT * FROM tablename WHERE dateFieldName='2009-10-27'
SELECT * FROM tablename WHERE dateFieldName=#2009-10-27#
SELECT * FROM tablename WHERE dateFieldName=`2009-10-27`or on a DATETIME field, same tests however replace = by >= all this assumes a new record got added with dateFieldName set to NOW() or CURDATE() or whatever yields the current date or datetime. And of course my entire test would sit in a try-catch with the catch displaying exception.ToString() :)
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
Thank you so much for your help! But...here is the report: Single quotes, '10/25/2009' returns an error of Type Mismatch in WHERE clause, value '10/25/2009' A pound around the date returns an error of Syntax Error: Required text missing. Next token '#' The ` character around the date returns the same as the previous. (thought this was the one that would work due to using ` in bash scripting and this being a *nix server) Using the DateTime field dtProcDate by itself returns an error of Unprocessed input. Next token '8' (which is the hour of the day) Doing the same on >= would yield the same result. The data is updated daily at midnight local time, so the data is static for the date used. And all is set in a try/catch. These pretzels are making me thirsty!
Jude
-
Thank you so much for your help! But...here is the report: Single quotes, '10/25/2009' returns an error of Type Mismatch in WHERE clause, value '10/25/2009' A pound around the date returns an error of Syntax Error: Required text missing. Next token '#' The ` character around the date returns the same as the previous. (thought this was the one that would work due to using ` in bash scripting and this being a *nix server) Using the DateTime field dtProcDate by itself returns an error of Unprocessed input. Next token '8' (which is the hour of the day) Doing the same on >= would yield the same result. The data is updated daily at midnight local time, so the data is static for the date used. And all is set in a try/catch. These pretzels are making me thirsty!
Jude
For the last time, it is bound to be #year-month-day# where # is some special character :|
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
-
For the last time, it is bound to be #year-month-day# where # is some special character :|
Luc Pattyn
I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages
Got It!!! Thanx!
Jude
