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).
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).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Sorry for being so lame on this, but date conversions are not my forte :D
R
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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...
Thank you for your help anyway, I really appreciate it...
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.
Thanks.
ASKER
Hi Ken... the statement is at the top of this post (it was in my initial post)...
R
R
Hi, R.
That statement used a date of the form dd/mm/yyyy. I'm suggesting the yyyy-mm-dd form.
Ken
That statement used a date of the form dd/mm/yyyy. I'm suggesting the yyyy-mm-dd form.
Ken
ASKER
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
Again, thanks so much for your help... I should have split the points evenly... oh well, next time!
R
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. :-)
Let me know if it doesn't work--but I'm sure it will. :-)
ASKER
I tried the following format strings:
DateTime.Parse(atomicDate,
DateTime.Parse(atomicDate,
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
DateTime.Parse(atomicDate,
DateTime.Parse(atomicDate,
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
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.
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.
ASKER
Oops... sorry, C# and .NET 2.0
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.
ASKER
Can we open this in another question so I can give you points for help on this?
Sure. Email me when you do, so I can find it.
ASKER
ID:22714507 Author:rmmarsh Date:07.23.2007 at 09:00AM PDT | Points: 500Question
convert(varchar, theDateTimeField, 120)