Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Fixing date format on table (sqlite)

Status
Not open for further replies.
Trevoke

Trevoke

Programmer
Joined
Jun 6, 2002
Messages
1,142
Location
US
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?
 
Sort by date Sort by votes
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?
 
Upvote 0 Downvote
However, far from ANSI compliant...
 
Upvote 0 Downvote
Trevoke said:
Is there an ANSI compliant answer?
Click to expand...

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
 
Upvote 0 Downvote
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);

 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

mmiah1982
  • Locked
  • Question Question
Extracting mutiple data items from DB with JSON string format column
Replies
2
Views
563
mmiah1982
mmiah1982
mikrom
  • Locked
  • Question Question
from a table with symmetric relation select every pair only once
Replies
0
Views
439
mikrom
mikrom
harfri
  • Locked
  • Question Question
Insert into a new table
Replies
0
Views
440
harfri
harfri
szeiss
  • Locked
  • Question Question
Need to select the one value if it exists otherwise another value
Replies
4
Views
622
szeiss
szeiss
jchewsmith
  • Locked
  • Question Question
Update Query with a MAX(Date) subquery
Replies
1
Views
688
JarlH
JarlH

Part and Inventory Search

Sponsor

Back
Top