SQL Server datetime error


#1

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’)


#2

i’m using Rails 1.0, Ruby 1.8.2 and SQL Server.


#3

I had a similar problem when using DateTime instead of Time objects.
Could this solve your problem?


#4

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.


#5

If you can pinpoint the problem, be sure to file a bug report on the
Rails website.

Jamie


#6

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.


#7

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 http://www.bigbold.com/snippets/posts/show/756

Regards,
Erik.


#8

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 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”


#9

No solution in sight?! Please help me, since i’m doomed to use
ColdFusion right now :slight_smile:


#10

Thibaut Barrère wrote:

Hi

you might want to have a look over there :
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.


#11

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 :slight_smile:

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

def before_save(record)
record.credit_card_number = encrypt(record.credit_card_number)
end

Thibaut


#12

Hi

you might want to have a look over there :
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


#13

there’s no parameter to the before_save callback, so it failed :frowning:

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?!


#14

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!)

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.


#15

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 :slight_smile:

i wish you good luck :slight_smile:


#16

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 :slight_smile:


#17

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

#18

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)…


#19

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.


#20

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