Re: [sqlite] String --> Integer date formatting
Dennis Cote wrote: > Emmanuel, > > Check out the functions on this page > http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions > > In particular the function julianday(datestring) will return a julian > day number for a suitably formatted date string. The supported date > formats are from ISO-8601, or -MM-DD, so you will have to use > substr and concatenation (||) to rearrange your dates. > > HTH > Dennis Cote Thanks Denis this was a very good suggestion. Here is the part of my query that converts "DD/MM/" to "-MM-DD", it works like a charm with julianday() substr(c.value,-4,4) || "-" || (CASE WHEN substr(c.value,-7,1) = '/' THEN "0" || substr(c.value,-6,1) ELSE substr(c.value,-7,2) END) || "-" || (CASE WHEN substr(c.value,2,1) = '/' THEN "0" || substr(c.value,1,1) ELSE substr(c.value,1,2) END) - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] String --> Integer date formatting
Emmanuel wrote: I have normal date fields (stored as an integer, and easily manipulated with date(), strftime(), etc.) I have one extra field that contains a date but is stored as a DD/MM/ string. I would like to calculate the number of days between the two. This would be an easy juliandate() substraction if both dates were of the same data type, but they aren't. I am looking for a function similar to Oracle's TO_DATE(string,'DD/MM/') which would convert my string into a numerical value representing that date. This would then allow me to use normal date functions to work on it. I have tried TO_DATE in SQLite but it didn't work. Is there an equivalent function? If not, is there any other way to achieve what I'm trying to do? Emmanuel, Check out the functions on this page http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions In particular the function julianday(datestring) will return a julian day number for a suitably formatted date string. The supported date formats are from ISO-8601, or -MM-DD, so you will have to use substr and concatenation (||) to rearrange your dates. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] String --> Integer date formatting
Hello, I am using SQLite as part of Trac (http://trac.edgewall.com/) to generate reports on our ticket system. I have normal date fields (stored as an integer, and easily manipulated with date(), strftime(), etc.) I have one extra field that contains a date but is stored as a DD/MM/ string. I would like to calculate the number of days between the two. This would be an easy juliandate() substraction if both dates were of the same data type, but they aren't. I am looking for a function similar to Oracle's TO_DATE(string,'DD/MM/') which would convert my string into a numerical value representing that date. This would then allow me to use normal date functions to work on it. I have tried TO_DATE in SQLite but it didn't work. Is there an equivalent function? If not, is there any other way to achieve what I'm trying to do? Thanks in advance for your insights. Regards - To unsubscribe, send email to [EMAIL PROTECTED] -
