Fixing date format on table (sqlite)

Question

Fixing date format on table (sqlite)

Trevoke (Programmer)

(OP)

25 Sep 09 12:06

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:

CODE

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 (Programmer) · Answer 1 · 2009-09-25 13:02:36

The answer is...

CODE

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 (Programmer) · Answer 2 · 2009-09-28 03:22:17

RE: Fixing date format on table (sqlite)

JarlH (Programmer) 28 Sep 09 03:22

However, far from ANSI compliant...

Trevoke (Programmer) · Answer 3 · 2009-09-28 08:01:39

Is there an ANSI compliant answer?

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

r937 (TechnicalUser) · Answer 4 · 2009-09-28 18:27:37

Quote (Trevoke):
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 (Programmer) · Answer 5 · 2009-09-29 02:24:31

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 (Programmer) · Answer 6 · 2009-09-29 08:42:06

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 (TechnicalUser) · Answer 7 · 2009-09-29 11:17:53

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 (Programmer) · Answer 8 · 2009-09-29 13:32:37

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?

Come Join Us!

Posting Guidelines

Fixing date format on table (sqlite)