convert a date format yyyy mm dd in sqllite

need a date format like this in sqllite2013-10-29

my query was SELECT strftime('%Y-%m-%d', '29/10/2013')

but it showing blank output.I don't know what is the problem.

MY ANS FOR DAY DIFF

================================= 

SELECT cast(julianday() - julianday((substr(date_time,7,4) || '-' ||  substr(date_time,4,2) || '-' || substr(date_time,0,3)))as integer) as DayDiff  from sms_table
Jon Skeet
people
quotationmark

According to the documentation, the second argument of strftime is meant to be a timestring, which need to be in one of the following formats:

  • YYYY-MM-DD
  • YYYY-MM-DD HH:MM
  • YYYY-MM-DD HH:MM:SS
  • YYYY-MM-DD HH:MM:SS.SSS
  • YYYY-MM-DDTHH:MM
  • YYYY-MM-DDTHH:MM:SS
  • YYYY-MM-DDTHH:MM:SS.SSS
  • HH:MM
  • HH:MM:SS
  • HH:MM:SS.SSS
  • now
  • DDDDDDDDDD

Your slash-separated string is non of these, so it can't be parsed.

I suggest you change the format before inserting the data into your database in the first place (assuming your query would normally be running over your data).

If you can't do that, you'll quite possibly need to use string manipulation either to convert straight to your preferred format, or into one of the above forms so that you can then use strftime.

people

See more on this question at Stackoverflow