conversion from CHAR to DATETIME error

on a column DateNew = DateTime

i am trying :
INSERT INTO [dbo].[Users] (DateNew) VALUES ('2003/01/31 10:04:14')

and i get an error :
conversion of char data type to datetime data type resulted in an out of range datetime value

I had never this error before , do you know why ?
i must enter a yyyy/mm/dd format because this database will be used for Fr and Us langages

thank you for helpingIm not getting any error with ur insert statement.I think u are passing date value as a variable which is in char.

try trim that variable on both side using ltrim,rtrim before inserting.|||lookup SET DATEFORMAT in Books online. Maybe that would help.|||You can try this -
insert <tablename>
select convert(datetime,'2003/01/31 10:04:14')

Well, for more info check this out...
http://groups.google.co.in/group/microsoft.public.sqlserver.programming/browse_frm/thread/ada70fc46e7005ac/419fae5346ac4bc0?lnk=st&q=dateformat()+in+ms+sql+server+200&rnum=3&hl=en#419fae5346ac4bc0|||try this instead --

INSERT INTO [dbo].[Users] (DateNew) VALUES ('2003-01-31 10:04:14')|||INSERT INTO [dbo].[Users] (DateNew) VALUES ('2003/01/31 10:04:14')

insert <tablename>
select convert(datetime,'2003/01/31 10:04:14')

or this one

try this instead --

INSERT INTO [dbo].[Users] (DateNew) VALUES ('2003-01-31 10:04:14')
__________________

These statements all are working fine in my machine also,so no problem in statements...I think mailler made a point ..plz check that.
Joydeep|||i got it with
INSERT INTO [dbo].[Users] (DateNew) VALUES (convert(datetime,'2003/01/31 10:04:14',111))

thank you|||i got it with convert(datetime,'2003/01/31 10:04:14',111)

thank you|||I know that I'm being pendantic here, but I'd invest a bit of time now into making your application much more portable/flexible/etc. The ISO 8601 (http://www.iso.org/iso/en/prods-services/popstds/datesandtime.html) format for date/time information is CCYY-MM-DD HH:MM:SS.TTT and that format is used by virtually the entire computing universe. It has been adopted by W3C (http://www.w3.org/TR/NOTE-datetime) which means that almost anywhere you find time on the Internet, you'll find it in this format.

You'll almost certainly save yourself lots of time and energy if you switch to using this format now, instead of having to switch to it later!

As a side note, if you expect your application to grow to the point where you may need to support more than one server, I'd suggest you spend the time to convert your application to use UCT (aka GMT) now too... This is easy to do up front, and almost impossible to do "after the fact" due to many very difficult problems caused by different locales.

-PatP|||...due to many very difficult problems caused by different locales...
PatP
You mean time zones, right?|||You mean time zones, right?You can think of the problem that way, but it is really more complex than just time zone... A locale rolls the problem up into a nice tidy (but not simple bundle). Time zones reflect a difference between local time and UCT, essentially a time offset. The problem comes from Daylight Savings Time, where different locales observe different shift dates, not all of which adjust by the same amount (some only move 30 minutes).

Unravelling the mess is easy if done while recording an event because it is easy for a computer to find UCT from its local time if necessary. Once the time is stored, there may not be any way to recover true UCT again. This gets really hard to explain, but there have been a couple of good whitepapers done on the problem.

-PatP|||Thanks a lot I shall do it at once but HOW do you convert a normal date into ISO 8601 ?

what is the SQL command for it ?|||for the moment I store all my dates time inthe format
yyyy/MM/dd hh:mm:ss

2000/12/31 18:50:06
I have just to replace / by - ?

thanks a lot|||for the moment I store all my dates time inthe format
yyyy/MM/dd hh:mm:ss

2000/12/31 18:50:06
I have just to replace / by - ?

thanks a lotYes! Exactly.

This is a relatively small change "up front", but it makes your date/time format match the format used by nearly everything else. That makes your code much easier to port to other programming languages, databases, etc at a later time. It is a small investment up front, that can pay off hugely in the future.

As a side note, SQL Server stores the data internally in a completely different form... Once you get the data into a column or variable, the work has been done. The only place you need to change anything is in the actual conversion from a character representation to a DATETIME.

-PatP|||Pat I did it , I have inserted 100 rows in my SQL database
in the good format but the database seems change it for the french format
31/12/2005 18:20:45

Once you get the data into a column or variable
I store the date in a datetime format column ?!

thanks a lot

and for searching any row in my database where a date
>
<
=
<>
=<
<=
to another date but on the date not on the datetime (yyyy-MM-dd) ?|||What is actually happening is that the database stores the value internally as a bunch of bits... They don't look like anything to the average human eye, and are logically close to a pair of integer counters. When your application retrieves the DATETIME value, the client component of the software converts those bits to a human readable form based on the locale that I mentioned in an earlier post, and the rules for that conversion happen to make the converted text appear "French" on your machine.

There are a number of ways to search for dates within a range (such as entered at any time on a given date). I prefer to do this by finding the minimum value (the very start of the day, at midnight) and the maximum value (or just past the maximum value if that is easier), then finding values between the minimum and maximum that I've selected. So for instance to find values that happened on Saint Valentine's Day 2006, I'd use:SELECT *
FROM myTable
WHERE '2006-02-14' <= myDate -- Note "equals"
AND myDate < '2006-02-15' -- Note no "equals"Using this logic is a bit strange at first, but it allows the database to use indicies to find dates of interested quickly and easily. That makes it possible to pick the rows for one day out of ten years worth of data in seconds instead of hours!

-PatP|||you helped me a lot Pat !!! in a few answers more than a few weeks looking everywhere, thanks a TON

a last question !!

I am using now

WHERE CONVERT(CHAR(10), myDate, 120) = CONVERT(CHAR(10), myDateValue, 120)

it is very easy with server side language to generate it, but for the database on millions of rows (the application will be very big) is it faster or slower than your method ?

WHERE '2006-02-14' <= myDate AND myDate < '2006-02-15'

because with your method I must add a day to the normal date and it is more complicated for server side programming

thanks again for helping|||I am using now

WHERE CONVERT(CHAR(10), myDate, 120) = CONVERT(CHAR(10), myDateValue, 120)

it is very easy with server side language to generate it, but for the database on millions of rows (the application will be very big) is it faster or slower than your method ?slower, much slower

first of all, you don't have to convert a datetime value such as '2006-02-15' to datetime, as you do on the right side of that condition, because the database will treat it that way (as a datetime value) by default

however, if you convert your table column to a string, as you do on the left side of that condition, then the database cannot use the index, if any, on that column, and will do a table scan

in other words, performing a function on a column means that the condition is not sargable (http://netknowledgenow.com:81/CS/blogs/onmaterialize/archive/2006/01/11/65.aspx) (this link is not working today but it was fine yesterday, it's a really good explanation -- you can also do a quick search to find other articles which also explain that word)|||then i must absolutly keep this only way ? :

SELECT FROM myTable
WHERE '2006-02-14' <= myDate AND myDate < '2006-02-15'

but in my database datetimes are stored in that was yyyy/mm/dd hh:mm:ss
and for the moment i couldnt get any row comparing yyyy/mm/dd hh:mm:ss to yyyy/mm/dd

of course the column is a datetime datatype|||then i must absolutly keep this only way ? :

SELECT FROM myTable
WHERE '2006-02-14' <= myDate AND myDate < '2006-02-15'that is the only way to achieve good performance (except you need to change the first operand from <= to >=)

but in my database datetimes are stored in that was yyyy/mm/dd hh:mm:ssno, actually, they are not stored that way -- datetimes are stored as two integers|||and is it better to use a datetime columns or a smalldatetime
all my dates are starting after 2000 ?

thank you|||that depends on whether you need precision in the time|||smalldatetime : Date and time data from January 1, 1900, through June 6, 2079,
with an accuracy of one minute

datetime :Date and time data from January 1, 1753, through December 31, 9999,
with an accuracy of 3.33 milliseconds

if i dont need (who needs ?) a precision of 1 minutes is it better for performances on millions of rows to use smalldatetime ?|||now I get all int that way and it seems to work :

-----------

>= 2006-02-10

SELECT FROM Users
WHERE (DateColumn > '2006-02-11')

-----------

< 2006-02-10

SELECT FROM Users
WHERE (DateColumn < '2006-02-10')
-----------
<= 2006-02-10

SELECT FROM Users
WHERE (DateColumn < '2006-02-11')
-----------

= 2006-02-10
SELECT FROM Users
WHERE
(DateColumn >= '2006-02-10')
AND
(DateColumn < '2006-02-11')

-----------

<> 2006-02-10

SELECT FROM Users
WHERE
(DateColumn > '2006-02-11')
OR
(DateColumn <= '2006-02-10')
-----------

>= 2006-02-15

SELECT FROM Users
WHERE (DateColumn < '2006-02-15')|||if i dont need (who needs ?) a precision of 1 minutes is it better for performances on millions of rows to use smalldatetime ?Yes, a SMALLDATETIME will perform better than a DATETIME for many reasons. Maybe looking at things from the machine's perspective will help (and maybe that will just confuse issues even more):DECLARE @.d DATETIME, @.s SMALLDATETIME

SELECT @.d = GetUTCDate()
SELECT @.s = @.d

SELECT @.d, Convert(VARBINARY(20), @.d)
SELECT @.s, Convert(VARBINARY(20), @.s)

SELECT @.d = DateAdd(minute, 1, @.d)
SELECT @.s = @.d

SELECT @.d, Convert(VARBINARY(20), @.d)
SELECT @.s, Convert(VARBINARY(20), @.s)-PatP|||thanks again a lot Pat that was really usefull, a deep help

thank you to everybody|||2006-02-16 03:10:53.967 | 0x0000976A00346E9E

2006-02-16 03:11:00 | 0x976A00BF

2006-02-16 03:11:53.967 | 0x0000976A0034B4EE

2006-02-16 03:11:53.967 | 0x0000976A0034B4EE

2006-02-16 03:12:00 | 0x976A00C0

here is the result of your Query
not easy to read and understand|||hard to understand?

these numbers -- 0x0000976A00346E9E, 0x976A00BF -- show you exactly how datetime values are stored internally in sql server

:)|||not easy to read and understandThe results show a couple of the issues that I was trying to explain, in a concrete form (so we don't have to talk abstractly, but can deal with real values. Please bear with me, this explanation is long, but I think it will help.

The first two results show the difference between a DATETIME (as displayed in character form) and how that DATETIME value converts to both raw binary (on the same line), and how it converts to a SMALLDATETIME (which appears one line down), and also to the SMALLDATETIME expressed as raw binary. All of the values are different, but they represent the same moment in time in different ways!

A DATETIME is accurate to +/- 3 milliseconds (I know the docs say 3.33, but that is a case where the doc writers took some liberty with what is actually stored... There's no such thing as a third of a bit). It is actually stored as a bunch of bits that mean very little to the untrained eye, except for one minor thing I'll get to later.

A SMALLDATETIME is accurate to +/- 1 minute. When you assign a DATETIME to a SMALLDATETIME, rounding takes place to the nearest whole minute. The same time is represented in a slightly less acurate form. The binary value is also quite a bit smaller, and radically different.

As an interesting side note (of little practical value), note that the value 976A appears in both of the binary strings (although in different places). This is not an accident. It has to do with how the date values are actually stored.

Another more interesting note is that the binary values of a DATETIME and the corresponding SMALLDATETIME are not directly comparable. If you take the time to understand the details, you can work around this, but it is of very little use except as an academic exercise.

Things become a bit more interesting when we add a minute to the original DATETIME value. The character form makes it easy to see this addition, and it makes perfect sense.

When we convert the changed DATETIME value to a SMALLDATETIME, the same rounding takes place, and the binary values are still quite different from each other.

The interesting part comes when you compare the binary values of the DATETIME before and after adding a minute, and the binary values of the SMALLDATETIME before and after adding a minute. The important part to notice is that the binary values of the later values have larger binary values too! There is a direct, one to one relationship between the time and the corresponding binary value.

This is why Rudy pointed out earlier that computing the minimum and maximum values of interest was much more efficient than converting the date values to character form and comparing them... You can convert a DATETIME into many different character forms, most of which are not usable for range computations like this, so in order to search for a character form SQL Server has to query every possible row. SQL Server understands dates as either DATETIME or SMALLDATETIME values, and knows how to search an index for values in a specific range. This means that it can "ride the index" to only the rows of interest in your range, and it knows exactly when it has reached the end of that range. For large sets of data, this is MANY times more efficient!

-PatP|||wow ! perfect !
I never like to apply something without understanding it
now I get a better idea of the way SQL is working with dates

and I think that generally i need only smalll
datetime datatype, i had never used it before

thanks a lot once more !|||One more thing to throw out, just so you don't get surprised... The SMALLDATETIME datatype allows entry of temporal (time based) values from 1900-01-01 through 2079-06-06 23:39. This is fine for many purposes (I won't be alive to deal with any problems it might cause when it runs out, and I sincerely doubt that SQL Server will still be in use (at least in its present form) 70 years from now! However, many contracts (like Japanese mortgages) already extend well past that limit, so I usually use DATETIME even though a SMALLDATETIME would do.

I'm a lazy bum... If I can code/create something once then safely forget about it, I'll almost always do that instead of something a bit simpler that will work for a while, but might be a problem for me (or my successor) in the future. I won't do a lot of work to avoid a potential problem, but I'll do easy things to avoid getting a call at 03:00 wondering why a job failed and how soon can I get it fixed!

-PatP|||Pat your code works fine for me in SQL 2005, but a customer with SQL 2000 get errors again everywhere on dates, i have started a new thread here >> http://dbforums.com/showthread.php?t=1212861

i am really lost with dates .. and i dont know what to do

thanks again