×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Fixing date format on table (sqlite)

Fixing date format on table (sqlite)

Fixing date format on table (sqlite)

(OP)
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?

RE: Fixing date format on table (sqlite)

(OP)
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?

RE: Fixing date format on table (sqlite)

However, far from ANSI compliant...

RE: Fixing date format on table (sqlite)

(OP)
Is there an ANSI compliant answer?

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

RE: Fixing date format on table (sqlite)

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

smile

 

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

RE: Fixing date format on table (sqlite)

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

 

RE: Fixing date format on table (sqlite)

(OP)
Understood, thanks.
I am indeed reformatting the datetime in that column. I guess there is no ANSI answer then winky smile

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

RE: Fixing date format on table (sqlite)

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

RE: Fixing date format on table (sqlite)

(OP)
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?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close