CODEWITHSUNDEEP
sqlite
shiroe
shiroe

Reputation: 617

select only time from datetime field in SQLite

In my table I have a datetime field that stores dates in this format:

YYYY/mm/dd HH:MM:SS

Now I need to retrieve via query only the time.

I have tried this

SELECT time(start_date)
FROM table
LIMIT 100

but no luck, it gives me "not an error" but no records return

Any idea?

EDIT

I solved it! The problem is that SQLite needs Times to be in a format in which if hours, minutes and seconds are less than 10 they must be represented with the zero.

For example:

          H:M:S
WRONG --> 12:1:30
RIGHT --> 12:01:30

Moreover, the correct format for dates is YYYY-mm-dd and not YYYY/mm/dd.

Upvotes: 9

Views: 8803

Answers (1)

nilay jha
nilay jha

Reputation: 754

There is a built-in function in SQLite called strftime(format,datetime) which can be used to get whatever piece of information you require from the given datetime. In your case you can use in this way:

SELECT strftime('%H:%M:%S',start_date) FROM table LIMIT 100;

Upvotes: 9

Related Questions