Fixing date format on table (sqlite)

[Trevoke]

I guess the first question is, will this update each cell with the proper data?

update table set date_cell = (magic involving date_cell)

And if so the next question is..
what's "magic" ?

Will I get the proper result with:

UPDATE mytable
SET date_cell = (SELECT strftime('%Y-%m-%d %H:%M:%S', date_cell); ) ;

?

Or do I need to do something more complicated, like a cursor?

Tao Te Ching Discussions : Chapter 9 (includes links to previous chapters)
What is the nature of conflict?

 

[Trevoke]

The answer is...

UPDATE mytable
SET date_cell = (SELECT strftime('%Y-%m-%d %H:%M:%S', date_cell) ) ;

This works.

Tao Te Ching Discussions : Chapter 9 (includes links to previous chapters)
What is the nature of conflict?

 

[JarlH]

However, far from ANSI compliant...

 

[Trevoke]

Is there an ANSI compliant answer?

Tao Te Ching Discussions : Chapter 9 (includes links to previous chapters)
What is the nature of conflict?

 

[r937]

Is there an ANSI compliant answer?

depends

you haven't explained what the "magic" is

it looks like you're setting a date value equal to a reformatted date value of the same date value

in ANSI, that'd be a "no op" -- don't touch it, because it's already a date





r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[JarlH]

And in ANSI SQL the SELECT has a FROM-clause.

This is syntactically correct:
UPDATE mytable
SET date_cell = strftime('%Y-%m-%d %H:%M:%S', date_cell);

 

[Trevoke]

Understood, thanks.
I am indeed reformatting the datetime in that column. I guess there is no ANSI answer then ;-)

Tao Te Ching Discussions : Chapter 9 (includes links to previous chapters)
What is the nature of conflict?

 

[r937]

well, see, that's just the point -- in ANSI SQL, a DATE is a date is a date

"reformatting" a date in place is meaningless

your date sounds like a VARCHAR

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Trevoke]

It's SQLite, and it was my first database honest-to-$deity implemented from scratch. I told it to be a datetime. I think it was a varchar anyway.
But now it's OK. I'm in Mysql. It's definitely a datetime. (I had issues doing the data transfer because some of the values in sqlite had microseconds tacked on).

Tao Te Ching Discussions : Chapter 9 (includes links to previous chapters)
What is the nature of conflict?