DATEADD

Hello,
Who can help me with this issue:
I've a database with a field "Value" that is of the data_type
nvarchar. There is also a field "Method" that has the value "DateTime"
if the content of the field "Value" has a date and time value in the
format "dd/mm/yyyy h:mm:ss".
If the record has the value "DateTime" in the "Method" field, then I
want to add 1 hour to the value in the "Value" field. I tried the
statement
UPDATE SoftCheck
SET [Value] = DATEADD(hh, 1, [Value])
WHERE (Method = 'FileDate')
but it gives me the error: "Arithmetic overflow error converting
expression to date type datetime". I tried different CAST and CONVERT
combination in the statement, but still get the error.
In fact it is a very simple action I want to perform. In my field the
value could be a date and time, but the datetype is a nvarchar. If the
value is a date and time, I want to add 1 hour since the summer/winter
hour change. Obviously this isn't so simple explained in a sql syntax
statement!?
Regards, Geert"Geerty" <geert.defevere@.roularta.be> wrote in message
news:4d1ada6.0503300426.36e699a4@.posting.google.com...
> Hello,
> Who can help me with this issue:
> I've a database with a field "Value" that is of the data_type
> nvarchar. There is also a field "Method" that has the value "DateTime"
> if the content of the field "Value" has a date and time value in the
> format "dd/mm/yyyy h:mm:ss".
> If the record has the value "DateTime" in the "Method" field, then I
> want to add 1 hour to the value in the "Value" field. I tried the
> statement
> UPDATE SoftCheck
> SET [Value] = DATEADD(hh, 1, [Value])
> WHERE (Method = 'FileDate')
> but it gives me the error: "Arithmetic overflow error converting
> expression to date type datetime". I tried different CAST and CONVERT
> combination in the statement, but still get the error.
> In fact it is a very simple action I want to perform. In my field the
> value could be a date and time, but the datetype is a nvarchar. If the
> value is a date and time, I want to add 1 hour since the summer/winter
> hour change. Obviously this isn't so simple explained in a sql syntax
> statement!?
Are you sure that everyone row with Method='FileDate' has a date string? I
can only get that same error message if there is a large integer used in the
DATEADD function (try 10000000). If the rows contained dates, albeit in a
format that SQL has trouble converting due to the dd/mm mm/dd orientation
issues, you'd get the following error:
"The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value."
Best to avoid using localised date formats and stick to the ISO format, as a
date such as 30/03/2005 (today in the UK) won't be converted by SQL if it
treats it as a US date (as the 3rd of the 30th month isn't valid, and you
get the above "out-of-range" error message).
First thing I'd do if I were you is try to find the row with the invalid
data in it.
Dan|||I've double checked the rows where the field "Method" has the value
'DateTime' and all have a date/time in the format dd/mm/yyyy h:mm:ss.
This is the short date and time notation from the OS.|||Geerty wrote on 30 Mar 2005 23:24:29 -0800:

> I've double checked the rows where the field "Method" has the value
> 'DateTime' and all have a date/time in the format dd/mm/yyyy h:mm:ss.
> This is the short date and time notation from the OS.
You had 'FileDate' as your Method expression match in your T-SQL, could that
have been the problem or was it just a typo?
It seems strange that you're getting that error message, and also having
problems using CONVERT. With the latter function you need to make sure you
use a conversion value that tells SQL that the date format is British.
You'll have problems with CAST as you'd need to use a date format that is
unambiguous, unlike the format you are using.
This should work to convert your dates to internal date format, add the
extra hour, and then convert back again. As the format you're using isn't
one of the built-in formats for Convert it gets a little messy.
UPDATE SoftCheck
SET [Value] = CONVERT ( nvarchar(10) , DATEADD ( hh, 1, CONVERT ( datetime ,
[Value] , 103 ) ) , 103 ) + ' ' + CONVERT ( nvarchar(8) , DATEADD ( hh , 1 ,
CONVERT ( datetime , [Value] , 103 ) ) , 108 )
WHERE (Method = 'DateTime')
If you still get an error, one (or more) of your [Value] rows is definitely
not in the date format you specified, or is something that SQL is evaluating
to a large integer.
There's probably a neater way to do this, but I've only just woken up :)
Dan