# SQL Server datetime error **URL:** \ **Category:** Rails\ **Created:** [January 20, 2006, 4:02pm UTC](https://www.ruby-forum.com/t/sql-server-datetime-error/63445 "2006-01-20T16:02:25Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![NoobSaibot](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/d2c977/32.png) [@NoobSaibot](https://www.ruby-forum.com/u/NoobSaibot)\ **Post date:** [January 20, 2006, 4:02pm UTC](https://www.ruby-forum.com/t/sql-server-datetime-error/63445/1 "2006-01-20T16:02:25Z")
i have a problem with the datetime format of rails. i am unable to save a dataset to the db. here’s the error message – the original error message was in german, so i translated it – i get: DBI::DatabaseError: Execute OLE error code:80040E07 in Microsoft OLE DB Provider for SQL Server Couldn’t convert a char-Datatype to datetime HRESULT error code:0x80020009 Exception occured.: INSERT INTO projects ([updated\_at], [title], [starts\_at], [description], [ends\_at], [created\_at]) VALUES(‘2006-01-20 15:23:40’, ‘title’, ‘2006-01-20 15:23:00’, ‘description’, ‘2006-01-20 15:23:00’, ‘2006-01-20 15:23:00’) ---
**Author:** ![NoobSaibot](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/d2c977/32.png) [@NoobSaibot](https://www.ruby-forum.com/u/NoobSaibot)\ **Post date:** [January 20, 2006, 4:12pm UTC](https://www.ruby-forum.com/t/sql-server-datetime-error/63445/2 "2006-01-20T16:12:50Z")
i’m using Rails 1.0, Ruby 1.8.2 and SQL Server. ---
**Author:** ![NoobSaibot](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/d2c977/32.png) [@NoobSaibot](https://www.ruby-forum.com/u/NoobSaibot)\ **Post date:** [January 20, 2006, 5:17pm UTC](https://www.ruby-forum.com/t/sql-server-datetime-error/63445/3 "2006-01-20T17:17:07Z")
I had a similar problem when using DateTime instead of Time objects. Could this solve your problem? ---
**Author:** ![NoobSaibot](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/d2c977/32.png) [@NoobSaibot](https://www.ruby-forum.com/u/NoobSaibot)\ **Post date:** [January 20, 2006, 5:29pm UTC](https://www.ruby-forum.com/t/sql-server-datetime-error/63445/4 "2006-01-20T17:29:33Z")
Jamie Orchard-Hays wrote: > I had a similar problem when using DateTime instead of Time objects. > Could this solve your problem? Right now i’m using scaffold, so i didn’t define the datatype of this attribute in the object. ---
**Author:** ![NoobSaibot](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/d2c977/32.png) [@NoobSaibot](https://www.ruby-forum.com/u/NoobSaibot)\ **Post date:** [January 20, 2006, 6:29pm UTC](https://www.ruby-forum.com/t/sql-server-datetime-error/63445/5 "2006-01-20T18:29:42Z")
If you can pinpoint the problem, be sure to file a bug report on the Rails website. Jamie ---
**Author:** ![NoobSaibot](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/d2c977/32.png) [@NoobSaibot](https://www.ruby-forum.com/u/NoobSaibot)\ **Post date:** [January 20, 2006, 6:20pm UTC](https://www.ruby-forum.com/t/sql-server-datetime-error/63445/6 "2006-01-20T18:20:07Z")
Well, i’d like to use Ruby (on Rails) in our company. But this stupid little problem prevents me from developing/presenting a usable application. ---
**Author:** ![NoobSaibot](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/d2c977/32.png) [@NoobSaibot](https://www.ruby-forum.com/u/NoobSaibot)\ **Post date:** [January 20, 2006, 8:18pm UTC](https://www.ruby-forum.com/t/sql-server-datetime-error/63445/7 "2006-01-20T20:18:36Z")
SQL Server has some weird problems with dates and internationalization. Some drivers (e.g. the Spanish one) even has bugs that will not be solved. Anyway, you might have more success with the following date formats: dates: {d ‘yyyy-mm-dd’}, for example {d ‘2001-12-31’} timestamps: {ts ‘yyyy-mm-dd hh:mm:ss’}, for example {ts ‘2001-12-31 00:00:00’} times: {t ‘hh:mm:ss’}, etc. See also [BigBold - Informasi Tentang Bisnis dan Marketing](http://www.bigbold.com/snippets/posts/show/756) Regards, Erik. ---
**Author:** ![NoobSaibot](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/d2c977/32.png) [@NoobSaibot](https://www.ruby-forum.com/u/NoobSaibot)\ **Post date:** [January 20, 2006, 11:48pm UTC](https://www.ruby-forum.com/t/sql-server-datetime-error/63445/8 "2006-01-20T23:48:41Z")
Erik van Oosten wrote: > SQL Server has some weird problems with dates and internationalization. > Some drivers (e.g. the Spanish one) even has bugs that will not be > solved. Anyway, you might have more success with the following date > formats: > > dates: {d ‘yyyy-mm-dd’}, for example {d ‘2001-12-31’} > timestamps: {ts ‘yyyy-mm-dd hh:mm:ss’}, for example {ts ‘2001-12-31 > 00:00:00’} > times: {t ‘hh:mm:ss’}, etc. > > See also [BigBold - Informasi Tentang Bisnis dan Marketing](http://www.bigbold.com/snippets/posts/show/756) > > Regards, > Erik. As you can see in my very first post – especially the error message – you’ll see that sql server does have problems with the format yyyy-mm-dd. However, i tried to reformat the date before it gets stored in the db. for that i used the before\_save/before\_create callback methods. def before\_save // first try @created\_at = @created\_at.strftime(“%d-%m-%y”) // second try self[:created\_at] = self[:created\_at].strftime(“%d-%m-%y”) // third try write\_attribute(:created\_at) = read\_attribute(:created\_at).strftime(“%d-%m-%y”) end another attempt to solve this problem was to redefine the getter method def created\_at // see before\_save end both attempts failed as i saw no changes in the date format. some of them even resulted in another error: “you tried to … nil.strftime” ---
**Author:** ![NoobSaibot](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/d2c977/32.png) [@NoobSaibot](https://www.ruby-forum.com/u/NoobSaibot)\ **Post date:** [January 24, 2006, 10:13am UTC](https://www.ruby-forum.com/t/sql-server-datetime-error/63445/9 "2006-01-24T10:13:08Z")
No solution in sight?! Please help me, since i’m doomed to use ColdFusion right now 🙂 ---
**Author:** ![NoobSaibot](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/d2c977/32.png) [@NoobSaibot](https://www.ruby-forum.com/u/NoobSaibot)\ **Post date:** [January 24, 2006, 11:20am UTC](https://www.ruby-forum.com/t/sql-server-datetime-error/63445/10 "2006-01-24T11:20:58Z")
Thibaut Barrère wrote: > Hi > > you might want to have a look over there : > [http://www.karaszi.com/SQLServer/info\_datetime.asp#DtFormatsInput](http://www.karaszi.com/SQLServer/info_datetime.asp#DtFormatsInput) > > Just like the authour, I tend to use (although not with rails > specifically > as I don’t use sql server with it) the unseparated dateformat (‘20051127 > 14:23:05’) which is language neutral. > > I have a trading station written in .Net which is deployed also in Spain > and > rely on that format, so I guess it should do the job there to. > > hope this helps > > Thibaut Thanks for the pointer. However, i wouldn’t be in the situation if i’d be able to format the date to whatever i want. in my last post i described my approach to this problem. Every approach failed, as i see no change in the datetime format which is used to actually save the data. Every hook (callback) i tried didn’t do the job. I’m unable to say if the fault’s on my end. However, i hope to find a way. ---
**Author:** ![NoobSaibot](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/d2c977/32.png) [@NoobSaibot](https://www.ruby-forum.com/u/NoobSaibot)\ **Post date:** [January 24, 2006, 11:36am UTC](https://www.ruby-forum.com/t/sql-server-datetime-error/63445/11 "2006-01-24T11:36:36Z")
> Thanks for the pointer. However, i wouldn’t be in the situation if i’d > be able to format the date to whatever i want. in my last post i > described my approach to this problem. Sounds right 🙂 Every approach failed, as i see no change in the datetime format which > is used to actually save the data. Every hook (callback) i tried didn’t > do the job. I’m unable to say if the fault’s on my end. Did you try putting a breakpoint into before\_save to inspect what’s available there ? Or another hint : I just browsed the doc and it seems that you miss a parameter to before\_save (but I never actually used it, so I may be wrong!) [http://api.rubyonrails.com/classes/ActiveRecord/Callbacks.html#M000652](http://api.rubyonrails.com/classes/ActiveRecord/Callbacks.html#M000652) def before\_save(record) record.credit\_card\_number = encrypt(record.credit\_card\_number) end Thibaut ---
**Author:** ![NoobSaibot](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/d2c977/32.png) [@NoobSaibot](https://www.ruby-forum.com/u/NoobSaibot)\ **Post date:** [January 24, 2006, 11:06am UTC](https://www.ruby-forum.com/t/sql-server-datetime-error/63445/12 "2006-01-24T11:06:24Z")
Hi you might want to have a look over there : [http://www.karaszi.com/SQLServer/info\_datetime.asp#DtFormatsInput](http://www.karaszi.com/SQLServer/info_datetime.asp#DtFormatsInput) Just like the authour, I tend to use (although not with rails specifically as I don’t use sql server with it) the unseparated dateformat (‘20051127 14:23:05’) which is language neutral. I have a trading station written in .Net which is deployed also in Spain and rely on that format, so I guess it should do the job there to. hope this helps Thibaut ---
**Author:** ![NoobSaibot](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/d2c977/32.png) [@NoobSaibot](https://www.ruby-forum.com/u/NoobSaibot)\ **Post date:** [January 24, 2006, 11:57am UTC](https://www.ruby-forum.com/t/sql-server-datetime-error/63445/13 "2006-01-24T11:57:33Z")
there’s no parameter to the before\_save callback, so it failed ☹ i set a breakpoint in the before\_save function and “puts” out the attributes in the object. _all_ of them were nil, even the self object! what’s wrong?! ---
**Author:** ![NoobSaibot](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/d2c977/32.png) [@NoobSaibot](https://www.ruby-forum.com/u/NoobSaibot)\ **Post date:** [January 24, 2006, 11:43am UTC](https://www.ruby-forum.com/t/sql-server-datetime-error/63445/14 "2006-01-24T11:43:32Z")
Thibaut Barrère wrote: > Did you try putting a breakpoint into before\_save to inspect what’s > available there ? i did. the result was always nil. > Or another hint : I just browsed the doc and it seems that you miss a > parameter to before\_save (but I never actually used it, so I may be > wrong!) > > [Peak Obsession](http://api.rubyonrails.com/classes/ActiveRecord/Callbacks.html#M000652) > > def before\_save(record) > record.credit\_card\_number = encrypt(record.credit\_card\_number) > end hmmm!! i’ll try that, thanks. ---
**Author:** ![NoobSaibot](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/d2c977/32.png) [@NoobSaibot](https://www.ruby-forum.com/u/NoobSaibot)\ **Post date:** [January 24, 2006, 1:26pm UTC](https://www.ruby-forum.com/t/sql-server-datetime-error/63445/15 "2006-01-24T13:26:05Z")
Thibaut Barrère wrote: > Did you try the various type of callback registrations as seen in the > doc ? the callbacks i’ve already mentioned in my previous posts and the one with a block: class MyClass \< BaseClass … before\_save { |record| record.created\_at = record.created\_at.strftime(“dd.mm.yyyy”) } end > (I never tried myself, but clearly I would start here - heck, I _will_ > start > here in a couple of days 🙂 i wish you good luck 🙂 ---
**Author:** ![NoobSaibot](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/d2c977/32.png) [@NoobSaibot](https://www.ruby-forum.com/u/NoobSaibot)\ **Post date:** [January 24, 2006, 1:13pm UTC](https://www.ruby-forum.com/t/sql-server-datetime-error/63445/16 "2006-01-24T13:13:39Z")
Did you try the various type of callback registrations as seen in the doc ? (I never tried myself, but clearly I would start here - heck, I _will_ start here in a couple of days 🙂 ---
**Author:** ![NoobSaibot](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/d2c977/32.png) [@NoobSaibot](https://www.ruby-forum.com/u/NoobSaibot)\ **Post date:** [January 24, 2006, 1:39pm UTC](https://www.ruby-forum.com/t/sql-server-datetime-error/63445/17 "2006-01-24T13:39:51Z")
btw. the breakpointer seems to be broken, as i get nil as output regardless what i do. ``` puts self[:created_at] => nil puts self => nil puts "not nil" => nil ``` am i doing something wrong? i set a breakpoint() in my controller. after this point is reached by the server/interpreter the web application is on hold. that’s where i do the following (in the app directory of course): ``` ruby script/breakpointer ``` ---
**Author:** ![NoobSaibot](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/d2c977/32.png) [@NoobSaibot](https://www.ruby-forum.com/u/NoobSaibot)\ **Post date:** [January 24, 2006, 1:53pm UTC](https://www.ruby-forum.com/t/sql-server-datetime-error/63445/18 "2006-01-24T13:53:18Z")
NoobSaibot wrote: > btw. the breakpointer seems to be broken, as i get nil as output > regardless what i do. > > ``` > puts self[:created_at] => nil > puts self => nil > puts "not nil" => nil > > ``` What do you get if you type ‘local\_variables’ and ‘instance\_variables’? (no puts)… ---
**Author:** ![NoobSaibot](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/d2c977/32.png) [@NoobSaibot](https://www.ruby-forum.com/u/NoobSaibot)\ **Post date:** [January 24, 2006, 2:08pm UTC](https://www.ruby-forum.com/t/sql-server-datetime-error/63445/19 "2006-01-24T14:08:31Z")
NoobSaibot wrote: > @project is in the list of instance\_variables What if you type ‘@project’? Sorry if this is going over old ground, but if I remember correctly (and I’ll be glad to be proved wrong here), the puts method dumps the data out to the original console the server’s running on, _not_ the breakpointer. The return value of puts is nil. ---
**Author:** ![NoobSaibot](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/d2c977/32.png) [@NoobSaibot](https://www.ruby-forum.com/u/NoobSaibot)\ **Post date:** [January 24, 2006, 2:01pm UTC](https://www.ruby-forum.com/t/sql-server-datetime-error/63445/20 "2006-01-24T14:01:56Z")
Alex Y. wrote: > NoobSaibot wrote: > > > btw. the breakpointer seems to be broken, as i get nil as output > > regardless what i do. > > > > ``` > > puts self[:created_at] => nil > > puts self => nil > > puts "not nil" => nil > > > > ``` > > What do you get if you type ‘local\_variables’ and ‘instance\_variables’? > (no puts)… @project is in the list of instance\_variables [Next page](https://www.ruby-forum.com/t/sql-server-datetime-error/63445.md?page=2)