2

I am trying to mimic the solution in this answer post: Sqlite convert string to date

The problem I am running into though is that my raw text date field contains dates in the following format:

1/5/2014 0:00:00

instead of:

01/05/2014 0:00:00

How can I account for this single digit month/day variability and adapt the code for my purpose? I cannot think of a decent way. I have thought of using excel to create a conversion table that I could join upon. Is there a more elegant approach?

Edit on 10/2/2014:

I would have thought there would be more like 81 possible combinations to identify. Or Maybe there is a simpler way I am not thinking of?

1/1/
1/2/
1/3/
1/4/
1/5/
1/6/
1/7/
1/8/
1/9/

2/1/
2/2/
2/3/
2/4/
2/5/
2/6/
2/7/
2/8/
2/9/

...
Community
  • 1
  • 1
timbram
  • 1,797
  • 5
  • 28
  • 49

1 Answers1

1

There are eight different combinations of one- or two-digit fields; you just have to check for all of them:

SELECT CASE
       WHEN Trans_Date LIKE '_/_/____ _:__:__'  THEN substr(Trans_Date, 10, 1)
       WHEN Trans_Date LIKE '_/_/____ __:__:__' THEN substr(Trans_Date, 10, 2)
       ...
       END AS Hour,
       ...
FROM LS2014
CL.
  • 173,858
  • 17
  • 217
  • 259
  • I would have expected there to be more like 81 possible combinations, right? Or maybe I thinking of it incorrectly? 1/1/ 1/2/ 1/3/ 1/4/ 1/5/ 1/6/ 1/7/ 1/8/ 1/9/ 2/1/ 2/2/ 2/3/ 2/4/ 2/5/ 2/6/ 2/7/ 2/8/ 2/9/ ... – timbram Oct 02 '14 at 14:36
  • The actual values do not matter; for `substr()` to work correctly, you just need to know the number of characters to skip. – CL. Oct 02 '14 at 14:42
  • Oh, that is a very good point! And actually, I may not be understanding your above code completely. What do the underscores do? They represent any value basically? – timbram Oct 02 '14 at 15:27
  • For some reason, I feel like SQLite documentation is hard to read. Anyway, I found this [T-SQL related link](http://msdn.microsoft.com/en-us/library/aa933232%28SQL.80%29.aspx) and I get it now!! I didn't even know you could use an _ like that in SQL! Your solution makes total sense now! :) – timbram Oct 02 '14 at 17:43