• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

Error in SQL statement caused by invalid date format

This is my select statement:  select COUNT(*) from tBooks where DateU > '20/07/2007 00:00:00'
The locale is en-GB; there is nothing in tBooks;

This is the error message I am getting:  The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

I can see why I'm getting it, but I thought that the locale would take care of this.  What do I have to do to the '20/07/2007' (which is in a variable of type DateTime)?

(For those of you who have been answering my previous questions and wonder what's going on, I am in the process of "internationalizing" my applicatation, and am making progress... one step at a time).
0
rmmarsh
Asked:
rmmarsh
  • 9
  • 8
2 Solutions
 
kenhaleyCommented:
Change the format of your date string to yyyy-mm-dd or yyyymmdd.
select COUNT(*) from tBooks where DateU > '2007-07-20 00:00:00'
The server, for whatever reason is interpreting your date as mm/dd/yyyy, even though your locale specifies dd/mm/yyyy.  By using the format above, you avoid the issue entirely--it works in all locales.
0
 
kenhaleyCommented:
To answer the other part of your question, if the date literal is coming from a datetime SQL field, you should convert it to a string as follows:
convert(varchar, theDateTimeField, 120)
0
 
rmmarshAuthor Commented:
Hi Ken... your conversion didn't work, but the idea is correct... I tried it as 20 JUL 2007 and it worked perfectly... now the question is: how do I convert '2007-07-20' to '20 JUL 2007'?

Sorry for being so lame on this, but date conversions are not my forte :D

R
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
JimBrandleyCommented:
Assuming you building the date string in an app and sending it to the server, Here's one in C#:

DateTime limitDate = DateTime.Parse("2007-07-20");
string dbParameterValue = limitDate.ToString("dd MMM yyyy");

0
 
kenhaleyCommented:
I don't understand how my conversion didn't work... I must not understand your environment.  Where (in what language) are you doing the conversion?
The problem with 20 JUL 2007 is that if you're really trying to make this international, JUL isn't recognized as July in all languages.  Only the yyyy-mm-dd syntax is universally recognized.
If you can explain the rest of your environment, I can probably help you create the code you need.
0
 
rmmarshAuthor Commented:
Hi Ken... right now, I'm just trying to convert this for English speaking European locale's... the yyy-mm-dd causes problems when inserting into the table.  I found that using "dd MMM yyyy" works for en-GB, and I would assume it would work for the other European countries also... however, you might be right about the JUL not being recognized in all languages.  I have to think about where I want to go on that idea.

Thank you for your help anyway, I really appreciate it...
0
 
kenhaleyCommented:
Thanks for your response..and the points.  However, I'm still surprised that you couldn't get the yyyy-mm-dd to work.  Our company sells a package internationally, and this format has consistently worked for us fpr a long time...  Out of curiosity can you post the exact SQL statement that's failing (that uses this format)?  Or just email it to me -- khaley4/at\gmai/dot\com.  

Thanks.
0
 
rmmarshAuthor Commented:
Hi Ken... the statement is at the top of this post (it was in my initial post)...

R
0
 
kenhaleyCommented:
Hi, R.

That statement used a date of the form dd/mm/yyyy.  I'm suggesting the yyyy-mm-dd  form.

Ken
0
 
rmmarshAuthor Commented:
How does it know that it's yyyy-mm-dd vs yyyy-dd-mm, or is not possible?  I can certainly give it a go tomorrow and see what happens...

Again, thanks so much for your help... I should have split the points evenly... oh well, next time!

R
0
 
kenhaleyCommented:
It's not possible.  There is no such format as yyyy-dd-mm.  2000-05-04 is universally recognized as 4 May 2000, and never 5 April 2000.  That's why I suggested the year-first syntax--it's bullet-proof.  (Don't worry about the points...it's the idea that's important.)

Let me know if it doesn't work--but I'm sure it will.  :-)
0
 
rmmarshAuthor Commented:
I tried the following format strings:

DateTime.Parse(atomicDate, "yyyy mm dd");  //  w/o dashes
DateTime.Parse(atomicDate, "yyyy-mm-dd");  //  with dashes

and each time it gave me an error message: "String was not recognized as a valid DateTime" when I went to use the results of the parse.  In each case, the date was returned as "2007 32 22" when using the current date. (I've never seen a date with the month of 32).

R
0
 
kenhaleyCommented:
What language is "DateTime.Parse" written in, and where does it live?

I thought you were working with T-SQL in a stored procedure or SQL script. This is what I meant when I asked about the environment.  I'm not getting the big picture here.  Is this Javascript? or C#?  (I'm guessing by the // form of the code comments.)  There's another function you need to be using to get the date converted to a string appropriate for SQL Server as you build the SQL query.  But it depends on what language you're using.
0
 
rmmarshAuthor Commented:
Oops... sorry, C# and .NET 2.0
0
 
kenhaleyCommented:
OK, so can you post the C# code that's building the query?  You should be using a parameterized command object or similar data access technique that allows you to pass parameters in a separate object.  Depending on what it is, I think I can show you how to fix it.  Plus, by using this technique properly, you'll automatically protect yourself against certatin types of security attacks (assuming your application will be exposed over the internet.)  The .NET framework can do the work for you.  I'm sure the issue is how you're building your query.
0
 
rmmarshAuthor Commented:
Can we open this in another question so I can give you points for help on this?
0
 
kenhaleyCommented:
Sure.  Email me when you do, so I can find it.
0
 
rmmarshAuthor Commented:
ID:22714507  Author:rmmarsh  Date:07.23.2007 at 09:00AM PDT | Points: 500Question
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now