-1

I tried to use the below statement to change DateTimes into SQLite desired DateTime format YYYY-MM-DD hh:mm so as to perform calculations. But the statement gives me an "incomplete input" message. Sample input-output is below:

e.g. 03/05/2020 17:34 => 2020-05-03 17:34

> UPDATE Combined_Bike_Ride SET started_at = substr(started_at,7,4) ||"-"|| substr(started_at(4,2)||"-"||substr(started_at(1,2)||" "||substr(started_at(12,2)||":"||substr(started_at(15,2)

What do you think is missing in the statement?

Ken White
  • 123,280
  • 14
  • 225
  • 444
bknmz
  • 21
  • 4

1 Answers1

0

for debugging in such cases, take "baby steps". Change content in a dummy column in a dummy test database. Then split your update command to smaller parts, adding one at a time.

It took about 2 minutes to see that you put a bracket where a comma was needed.

UPDATE dt 
    SET 
       md = 
       substr(sa,7,4) ||"-"|| 
       substr(sa,4,2)||"-"||
       substr(sa,1,2)||" "|| 
       substr(sa,12,2)||":"||
       substr(sa,15,2)

You had substr(sa(4,2) in most of them, making it an (incomplete) function.

Btw, it's perfectly OK to split the SQL statement over lines, which makes it more readable.

Most important lesson: in debugging take baby steps

Notice btw that your function will fail if one of the dates do not have leading zero. It is more reliable to do this in a programming language, as discussed here

MyICQ
  • 987
  • 1
  • 9
  • 25