I'm trying to import some SQL Server data into Umbraco but I get the following error for half the rows: "Arithmetic overflow error converting expression to data type datetime. The statement has been terminated."
What's happening is dates such as "2008-08-13" are being converted to "08-13-2008", i.e. from UK to US date formats so it obviously doesn't like it.
Trouble is I don't know how to configure the import to fix the problem! Any ideas?
I assume you are using the 2.0 version? Click on the green plus sign then you can specify the date format and that format will be used to convert the string value to date and then it should import ok.
Yes I am using 2.0 and I have been playing with the date format field but I still can't get it to work. Maybe I'm not using it properly, am I specifying of the format the incoming date, or the date structure I want to convert to, or something else?
So an example date that I'm trying to import is "2008-08-13 00:00:00.000", I've tried "yyyy/MM/dd HH:mm:sss" and "dd/MM/yyyy HH:mm:sss" but I get the same error.
I'm mapping against a dynamic property, but the problem occurs on both.
I'm importing data onto the Blog 4 Umbrcaco document type 'Blog Post', field Post Date. I have tried to map to the 'Publish At' field as well but I get the same error.
I just ran a test and when I mapped against the create data, or publish date with the specified format, make sure to include the time option as well it did work. It could be that your sqlserver is displaying a format, but in reality is using another format.Maybe specifying the format using a cast in your sql server query can fix that issue? Only thing that is a bug is the post field. This is a default date field and is missing the option to specify the Dateformat. I will fix that tomorrow.
A bit later than expected. I've created a hotfix which adds the date settings also to a date picker. I ran a few tests this morning and at first I got the same error as you had. When you have dates formatted with a single digit you should also specify a single digit in the formatting option. Best to explain by my weird looking test case.
When I have a date like this _31_1_2011 13:01 you can't use _dd_MM_yyyy HH:mm you must use _d_M_yyyy HH:mm this will pick up single and double digit date formats.
Hope this fixes your issue, if not please let me know.
Error importing dates
I'm trying to import some SQL Server data into Umbraco but I get the following error for half the rows:
"Arithmetic overflow error converting expression to data type datetime. The statement has been terminated."
What's happening is dates such as "2008-08-13" are being converted to "08-13-2008", i.e. from UK to US date formats so it obviously doesn't like it.
Trouble is I don't know how to configure the import to fix the problem! Any ideas?
Hi,
I assume you are using the 2.0 version? Click on the green plus sign then you can specify the date format and that format will be used to convert the string value to date and then it should import ok.
Cheers,
Richard
Firstly thanks for your prompt reply.
Yes I am using 2.0 and I have been playing with the date format field but I still can't get it to work. Maybe I'm not using it properly, am I specifying of the format the incoming date, or the date structure I want to convert to, or something else?
So an example date that I'm trying to import is "2008-08-13 00:00:00.000", I've tried "yyyy/MM/dd HH:mm:sss" and "dd/MM/yyyy HH:mm:sss" but I get the same error.
What am I doing wrong?
Hi,
It's the format you are importing from so my guess is you need to use "yyyy-MM-dd HH:mm:sss"
Cheers,
Richard
I've tried the following all resulting in the same error:
yyyy-MM-dd HH:mm:sss
yyyy-MM-dd HH:mm:ss
yyyy-MM-dd
Do you have any other ideas?
Are you mapping against a fixed property (CreateDate etc) or a dynamic property?
Thanks,
Richard
I'm mapping against a dynamic property, but the problem occurs on both.
I'm importing data onto the Blog 4 Umbrcaco document type 'Blog Post', field Post Date. I have tried to map to the 'Publish At' field as well but I get the same error.
Hi,
I just ran a test and when I mapped against the create data, or publish date with the specified format, make sure to include the time option as well it did work. It could be that your sqlserver is displaying a format, but in reality is using another format.Maybe specifying the format using a cast in your sql server query can fix that issue? Only thing that is a bug is the post field. This is a default date field and is missing the option to specify the Dateformat. I will fix that tomorrow.
Cheers,
Richard
HI,
A bit later than expected. I've created a hotfix which adds the date settings also to a date picker. I ran a few tests this morning and at first I got the same error as you had. When you have dates formatted with a single digit you should also specify a single digit in the formatting option. Best to explain by my weird looking test case.
When I have a date like this _31_1_2011 13:01 you can't use _dd_MM_yyyy HH:mm you must use _d_M_yyyy HH:mm this will pick up single and double digit date formats.
Hope this fixes your issue, if not please let me know.
Thanks,
Richard
Hi,
Yes that hotfix worked, I didn't have to provide any specific formatting it just worked with default settings :D
Thanks for taking the time to look at this problem, I really appreciate it.
Thanks
H
Check this small blogpost when you run into formatting issues.
Hope that helps,
Richard
is working on a reply...