-2

I have a string column in my table and values are in "05/31/2016 10:03:24.175000000" this format. Need to convert this string value into DateTime through SQLite.

I am using below query to convert string to date time. It's working fine for the "MM/dd/yyyy HH:MM:SS" this type of format and returns the valid result

Select datetime("05/31/2016 10:03:24") from table

While executing the below query it returns the empty as result. How to convert the below string to date time in SQLite; is this possible?

Select datetime("05/31/2016 10:03:24.175000000") from table
halfer
  • 19,824
  • 17
  • 99
  • 186
Umapathy S
  • 220
  • 2
  • 17
  • The first query does not work. And I recommend reading the [documentation](http://www.sqlite.org/lang_datefunc.html). – CL. Aug 10 '16 at 10:23
  • @MattWilko SQLite doesn't have a date type. But they *strongly* suggest to use the ISO8601 format otherwise the date functions won't work – Panagiotis Kanavos Aug 10 '16 at 10:23
  • @UmapathyS fix your data. SQLite [date functions](https://www.sqlite.org/lang_datefunc.html) work with the ISO8601 format. The [documentation on date and time data](https://www.sqlite.org/datatype3.html#section_2_2) explains that clearly. Or use `strftime` to parse the text – Panagiotis Kanavos Aug 10 '16 at 10:26
  • 1
    Try `strftime("%m/%d/%Y %H:%M:%S",someUsDateTextField)` to parse the text as a US-specific datetime format. It's far easier though to simply follow the convention of using ISO8601 strings – Panagiotis Kanavos Aug 10 '16 at 10:30
  • Thanks for your reply. I have checked strftime() function, it will return the result in specified format. in my case data is not in standard date time format, Additional digits was added in the end of string value so it always return the result as null for both datetime and strftime function. – Umapathy S Aug 10 '16 at 12:10

1 Answers1

-1

Well, if the table size is not too big you can define new column(datetime) and then write a script which copies values from string value to the new column.

Harisha V
  • 1
  • 1