Re: [sqlite] Converting date from d/m/yy format
Hi Kees and John, If you are getting data and storing it in an Sqlite database why not store it in the appropriate format? I will be storing it in the appropriate format. That's the objective of this solution, to convert the data into the appropriate format. I just want to do it within SQL. I would preprocess the data with an awk script. Perl or PHP or many other scripting tools are also suitable. Although it can be done, SQL just isn't the most elegant language to do these kind of things. It may not be the most elegant, but it is the criteria of my question ;-) In this small project I currently have no need for any other scripting language, so don't want to introduce one when SQL does the job (however inelegant). So, is there no better way to convert the date than the method I detailed? Thanks, Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Converting date from d/m/yy format
Thanks for the reminder. My ntp server seems to have died. Daniel Önnerby wrote: Sorry for this off topic comment: John, about the dates, could you please fix your system clock, your emails all appear at the top when sorting by date ;) Again, sorry for the off topic. Hope I'm not intruding :) Best regards Daniel John Stanton wrote: Our business for many years was producing compilers and database software to transport legacy software onto new platforms. We saw literally thousands of custom application software implementations and got to see the good, the bad and the ugly. As I said before dates were a blind spot with most developers and they got themselves into much quite avoidable trouble by jumping in without doing some research. Those who implemented the classic date/time system with an epoch in 4712BC (from memory) did the best. Lee Crain wrote: John, There are an almost infinite number of applications and operations that a person can think of regarding the use of dates and times. My employer's applications on that particular server were specific and well defined. I'm not going to go into all the details of the requirement; there were many. I picked the best, low-tech solution that satisfied every existing requirement and every foreseeable requirement. It was a very successful implementation that solved all date-time related problems on that server. And, (this is a very important point) if additional, unforeseen, date-time functionality must be implemented in the future, like adding or subtracting time intervals, those solutions can and should be implemented in the server software, not the server database. In my solution, the database is the repository of data, not logic. Lee Crain -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Monday, December 03, 2007 12:55 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Converting date from d/m/yy format I didn't mean to nitpick, but my experience has been that date and time processing is a blind spot. Over the years we have come across the most unholy kludges and nasty errors as people try to process dates without using the core theory. The Y2K situation was just one of the side effects in an ongoing litany of difficulties. Lee Crain wrote: John, None of the functionalities you mentioned were requirements on the project I worked on. Clumsy? My approach simplified everything on that server and is in production worldwide, and unmodified, today. Lee Crain ___ -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Monday, December 03, 2007 12:18 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Converting date from d/m/yy format That approach makes date processing clumsy. Distributing dates across time zones and into different calendars is difficult. Date arithmetic is awkward. When you use dates and times based on the magic astronomical epoch everything is simplified. Time zones and calendars are handled fairly transparently. Lee Crain wrote: Several years ago when I worked for a Fortune 70 company, we had a server whose source code and database were complicated by timestamps. I say complicated because there were different timestamp datatypes used for different fields (inherited from the data sources), the data could be stored in different formats, timestamp precision varied, and it was a problem to translate back and forth between the different representations and compare them. All of this added up to the occasional exercise of some obscure bugs in the server. I successfully undertook a project to fix this for all time. My solution was very simple: all timestamps were represented as strings in the following format: "MMDD:HHmmSS.nn" This format, no what the original data source or format, became the standard format for timestamps on this particular server. Precision was to the microsecond for all data, even if represented by zeroes. This had several virtues: When debugging software, all timestamps were readable when using Debug. Instead of looking at some binary number, the timestamp was easily human readable. When using administrative tools to access the database, it was easy to examine, modify, and compare timestamps, since they were all human readable and in exactly the same format. When comparing timestamps in the software to determine the most current, a simple string comparison always produced the correct result. The only feature that might have been needed (but wasn't on this particular server) was the ability to add or subtract time intervals from the timestamp. You may wish to consider a similar approach to managing your date and time information. Lee Crain -- --- To unsubscr
Re: [sqlite] Converting date from d/m/yy format
Sorry for this off topic comment: John, about the dates, could you please fix your system clock, your emails all appear at the top when sorting by date ;) Again, sorry for the off topic. Hope I'm not intruding :) Best regards Daniel John Stanton wrote: Our business for many years was producing compilers and database software to transport legacy software onto new platforms. We saw literally thousands of custom application software implementations and got to see the good, the bad and the ugly. As I said before dates were a blind spot with most developers and they got themselves into much quite avoidable trouble by jumping in without doing some research. Those who implemented the classic date/time system with an epoch in 4712BC (from memory) did the best. Lee Crain wrote: John, There are an almost infinite number of applications and operations that a person can think of regarding the use of dates and times. My employer's applications on that particular server were specific and well defined. I'm not going to go into all the details of the requirement; there were many. I picked the best, low-tech solution that satisfied every existing requirement and every foreseeable requirement. It was a very successful implementation that solved all date-time related problems on that server. And, (this is a very important point) if additional, unforeseen, date-time functionality must be implemented in the future, like adding or subtracting time intervals, those solutions can and should be implemented in the server software, not the server database. In my solution, the database is the repository of data, not logic. Lee Crain -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Monday, December 03, 2007 12:55 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Converting date from d/m/yy format I didn't mean to nitpick, but my experience has been that date and time processing is a blind spot. Over the years we have come across the most unholy kludges and nasty errors as people try to process dates without using the core theory. The Y2K situation was just one of the side effects in an ongoing litany of difficulties. Lee Crain wrote: John, None of the functionalities you mentioned were requirements on the project I worked on. Clumsy? My approach simplified everything on that server and is in production worldwide, and unmodified, today. Lee Crain ___ -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Monday, December 03, 2007 12:18 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Converting date from d/m/yy format That approach makes date processing clumsy. Distributing dates across time zones and into different calendars is difficult. Date arithmetic is awkward. When you use dates and times based on the magic astronomical epoch everything is simplified. Time zones and calendars are handled fairly transparently. Lee Crain wrote: Several years ago when I worked for a Fortune 70 company, we had a server whose source code and database were complicated by timestamps. I say complicated because there were different timestamp datatypes used for different fields (inherited from the data sources), the data could be stored in different formats, timestamp precision varied, and it was a problem to translate back and forth between the different representations and compare them. All of this added up to the occasional exercise of some obscure bugs in the server. I successfully undertook a project to fix this for all time. My solution was very simple: all timestamps were represented as strings in the following format: "MMDD:HHmmSS.nn" This format, no what the original data source or format, became the standard format for timestamps on this particular server. Precision was to the microsecond for all data, even if represented by zeroes. This had several virtues: When debugging software, all timestamps were readable when using Debug. Instead of looking at some binary number, the timestamp was easily human readable. When using administrative tools to access the database, it was easy to examine, modify, and compare timestamps, since they were all human readable and in exactly the same format. When comparing timestamps in the software to determine the most current, a simple string comparison always produced the correct result. The only feature that might have been needed (but wasn't on this particular server) was the ability to add or subtract time intervals from the timestamp. You may wish to consider a similar approach to managing your date and time information. Lee Crain -- --- To unsubscr
Re: [sqlite] Converting date from d/m/yy format
Our business for many years was producing compilers and database software to transport legacy software onto new platforms. We saw literally thousands of custom application software implementations and got to see the good, the bad and the ugly. As I said before dates were a blind spot with most developers and they got themselves into much quite avoidable trouble by jumping in without doing some research. Those who implemented the classic date/time system with an epoch in 4712BC (from memory) did the best. Lee Crain wrote: John, There are an almost infinite number of applications and operations that a person can think of regarding the use of dates and times. My employer's applications on that particular server were specific and well defined. I'm not going to go into all the details of the requirement; there were many. I picked the best, low-tech solution that satisfied every existing requirement and every foreseeable requirement. It was a very successful implementation that solved all date-time related problems on that server. And, (this is a very important point) if additional, unforeseen, date-time functionality must be implemented in the future, like adding or subtracting time intervals, those solutions can and should be implemented in the server software, not the server database. In my solution, the database is the repository of data, not logic. Lee Crain -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Monday, December 03, 2007 12:55 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Converting date from d/m/yy format I didn't mean to nitpick, but my experience has been that date and time processing is a blind spot. Over the years we have come across the most unholy kludges and nasty errors as people try to process dates without using the core theory. The Y2K situation was just one of the side effects in an ongoing litany of difficulties. Lee Crain wrote: John, None of the functionalities you mentioned were requirements on the project I worked on. Clumsy? My approach simplified everything on that server and is in production worldwide, and unmodified, today. Lee Crain ___ -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Monday, December 03, 2007 12:18 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Converting date from d/m/yy format That approach makes date processing clumsy. Distributing dates across time zones and into different calendars is difficult. Date arithmetic is awkward. When you use dates and times based on the magic astronomical epoch everything is simplified. Time zones and calendars are handled fairly transparently. Lee Crain wrote: Several years ago when I worked for a Fortune 70 company, we had a server whose source code and database were complicated by timestamps. I say complicated because there were different timestamp datatypes used for different fields (inherited from the data sources), the data could be stored in different formats, timestamp precision varied, and it was a problem to translate back and forth between the different representations and compare them. All of this added up to the occasional exercise of some obscure bugs in the server. I successfully undertook a project to fix this for all time. My solution was very simple: all timestamps were represented as strings in the following format: "MMDD:HHmmSS.nn" This format, no what the original data source or format, became the standard format for timestamps on this particular server. Precision was to the microsecond for all data, even if represented by zeroes. This had several virtues: When debugging software, all timestamps were readable when using Debug. Instead of looking at some binary number, the timestamp was easily human readable. When using administrative tools to access the database, it was easy to examine, modify, and compare timestamps, since they were all human readable and in exactly the same format. When comparing timestamps in the software to determine the most current, a simple string comparison always produced the correct result. The only feature that might have been needed (but wasn't on this particular server) was the ability to add or subtract time intervals from the timestamp. You may wish to consider a similar approach to managing your date and time information. Lee Crain -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- ---
RE: [sqlite] Converting date from d/m/yy format
John, There are an almost infinite number of applications and operations that a person can think of regarding the use of dates and times. My employer's applications on that particular server were specific and well defined. I'm not going to go into all the details of the requirement; there were many. I picked the best, low-tech solution that satisfied every existing requirement and every foreseeable requirement. It was a very successful implementation that solved all date-time related problems on that server. And, (this is a very important point) if additional, unforeseen, date-time functionality must be implemented in the future, like adding or subtracting time intervals, those solutions can and should be implemented in the server software, not the server database. In my solution, the database is the repository of data, not logic. Lee Crain -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Monday, December 03, 2007 12:55 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Converting date from d/m/yy format I didn't mean to nitpick, but my experience has been that date and time processing is a blind spot. Over the years we have come across the most unholy kludges and nasty errors as people try to process dates without using the core theory. The Y2K situation was just one of the side effects in an ongoing litany of difficulties. Lee Crain wrote: > John, > > None of the functionalities you mentioned were requirements on the project > I worked on. > > Clumsy? My approach simplified everything on that server and is in > production worldwide, and unmodified, today. > > Lee Crain > > ___ > > > -Original Message- > From: John Stanton [mailto:[EMAIL PROTECTED] > Sent: Monday, December 03, 2007 12:18 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Converting date from d/m/yy format > > > That approach makes date processing clumsy. Distributing dates across > time zones and into different calendars is difficult. Date arithmetic > is awkward. > > When you use dates and times based on the magic astronomical epoch > everything is simplified. Time zones and calendars are handled fairly > transparently. > > > Lee Crain wrote: >> Several years ago when I worked for a Fortune 70 company, we had a > server >> whose source code and database were complicated by timestamps. I say >> complicated because there were different timestamp datatypes used for >> different fields (inherited from the data sources), the data could be >> stored in different formats, timestamp precision varied, and it was a >> problem to translate back and forth between the different > representations >> and compare them. All of this added up to the occasional exercise of > some >> obscure bugs in the server. >> >> I successfully undertook a project to fix this for all time. My solution >> was very simple: all timestamps were represented as strings in the >> following format: >> >> "MMDD:HHmmSS.nn" >> >> This format, no what the original data source or format, became the >> standard format for timestamps on this particular server. Precision was > to >> the microsecond for all data, even if represented by zeroes. >> >> This had several virtues: >>> When debugging software, all timestamps were readable when using Debug. >> Instead of looking at some binary number, the timestamp was easily human >> readable. >>> When using administrative tools to access the database, it was easy to >> examine, modify, and compare timestamps, since they were all human >> readable and in exactly the same format. >>> When comparing timestamps in the software to determine the most > current, >> a simple string comparison always produced the correct result. >> >> The only feature that might have been needed (but wasn't on this >> particular server) was the ability to add or subtract time intervals > from >> the timestamp. >> >> You may wish to consider a similar approach to managing your date and > time >> information. >> >> Lee Crain >> > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > > > -- --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- --- > -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Converting date from d/m/yy format
I didn't mean to nitpick, but my experience has been that date and time processing is a blind spot. Over the years we have come across the most unholy kludges and nasty errors as people try to process dates without using the core theory. The Y2K situation was just one of the side effects in an ongoing litany of difficulties. Lee Crain wrote: John, None of the functionalities you mentioned were requirements on the project I worked on. Clumsy? My approach simplified everything on that server and is in production worldwide, and unmodified, today. Lee Crain ___ -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Monday, December 03, 2007 12:18 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Converting date from d/m/yy format That approach makes date processing clumsy. Distributing dates across time zones and into different calendars is difficult. Date arithmetic is awkward. When you use dates and times based on the magic astronomical epoch everything is simplified. Time zones and calendars are handled fairly transparently. Lee Crain wrote: Several years ago when I worked for a Fortune 70 company, we had a server whose source code and database were complicated by timestamps. I say complicated because there were different timestamp datatypes used for different fields (inherited from the data sources), the data could be stored in different formats, timestamp precision varied, and it was a problem to translate back and forth between the different representations and compare them. All of this added up to the occasional exercise of some obscure bugs in the server. I successfully undertook a project to fix this for all time. My solution was very simple: all timestamps were represented as strings in the following format: "MMDD:HHmmSS.nn" This format, no what the original data source or format, became the standard format for timestamps on this particular server. Precision was to the microsecond for all data, even if represented by zeroes. This had several virtues: When debugging software, all timestamps were readable when using Debug. Instead of looking at some binary number, the timestamp was easily human readable. When using administrative tools to access the database, it was easy to examine, modify, and compare timestamps, since they were all human readable and in exactly the same format. When comparing timestamps in the software to determine the most current, a simple string comparison always produced the correct result. The only feature that might have been needed (but wasn't on this particular server) was the ability to add or subtract time intervals from the timestamp. You may wish to consider a similar approach to managing your date and time information. Lee Crain -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Converting date from d/m/yy format
John, None of the functionalities you mentioned were requirements on the project I worked on. Clumsy? My approach simplified everything on that server and is in production worldwide, and unmodified, today. Lee Crain ___ -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Monday, December 03, 2007 12:18 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Converting date from d/m/yy format That approach makes date processing clumsy. Distributing dates across time zones and into different calendars is difficult. Date arithmetic is awkward. When you use dates and times based on the magic astronomical epoch everything is simplified. Time zones and calendars are handled fairly transparently. Lee Crain wrote: > Several years ago when I worked for a Fortune 70 company, we had a server > whose source code and database were complicated by timestamps. I say > complicated because there were different timestamp datatypes used for > different fields (inherited from the data sources), the data could be > stored in different formats, timestamp precision varied, and it was a > problem to translate back and forth between the different representations > and compare them. All of this added up to the occasional exercise of some > obscure bugs in the server. > > I successfully undertook a project to fix this for all time. My solution > was very simple: all timestamps were represented as strings in the > following format: > > "MMDD:HHmmSS.nn" > > This format, no what the original data source or format, became the > standard format for timestamps on this particular server. Precision was to > the microsecond for all data, even if represented by zeroes. > > This had several virtues: >> When debugging software, all timestamps were readable when using Debug. > Instead of looking at some binary number, the timestamp was easily human > readable. >> When using administrative tools to access the database, it was easy to > examine, modify, and compare timestamps, since they were all human > readable and in exactly the same format. >> When comparing timestamps in the software to determine the most current, > a simple string comparison always produced the correct result. > > The only feature that might have been needed (but wasn't on this > particular server) was the ability to add or subtract time intervals from > the timestamp. > > You may wish to consider a similar approach to managing your date and time > information. > > Lee Crain > -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Converting date from d/m/yy format
Lee Crain wrote: Several years ago when I worked for a Fortune 70 company, we had a server whose source code and database were complicated by timestamps. I say complicated because there were different timestamp datatypes used for different fields (inherited from the data sources), the data could be stored in different formats, timestamp precision varied, and it was a problem to translate back and forth between the different representations and compare them. All of this added up to the occasional exercise of some obscure bugs in the server. I successfully undertook a project to fix this for all time. My solution was very simple: all timestamps were represented as strings in the following format: "MMDD:HHmmSS.nn" This format, no what the original data source or format, became the standard format for timestamps on this particular server. Precision was to the microsecond for all data, even if represented by zeroes. This had several virtues: When debugging software, all timestamps were readable when using Debug. Instead of looking at some binary number, the timestamp was easily human readable. When using administrative tools to access the database, it was easy to examine, modify, and compare timestamps, since they were all human readable and in exactly the same format. When comparing timestamps in the software to determine the most current, a simple string comparison always produced the correct result. The only feature that might have been needed (but wasn't on this particular server) was the ability to add or subtract time intervals from the timestamp. You may wish to consider a similar approach to managing your date and time information. Lee Crain That approach makes date processing clumsy. Distributing dates across time zones and into different calendars is difficult. Date arithmetic is awkward. When you use dates and times based on the magic astronomical epoch everything is simplified. Time zones and calendars are handled fairly transparently. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Converting date from d/m/yy format
[Default] On Fri, 2 Nov 2007 17:29:23 +1100, T&B <[EMAIL PROTECTED]> wrote: >Hi all, > >How can I convert dates from the format d/m/yy to SQL style YYY-MM-DD? > >I have some imported data that includes a date column in the format d/ >m/yy, where: > >d = day as 1 or two digits >m = month as 1 or two digits >yy = year as two digits > >eg: > >2/11/07 = today >2/8/68 = 2nd of August, 1968 > >How can I convert this in SQLite to -MM-DD? > >The data is from a bank, so I have no control over its production. > >I couldn't find any suitable built in SQLite functions, which all seem >to operate in the other direction. > >The best I've come up with so far is: > >create table Raw( Date ); >insert into Raw( Date ) values ( '2/11/07' ); >insert into Raw( Date ) values ( '2/8/68' ); >select > case /* Prefix for year = 20 if year starts 0, 1 or 2, else 19 > */ > when cast( substr( Date, -2, 1 ) as integer ) < 3 > then '20' > else '19' > end > || substr( Date, -2, 2 ) /* Year = last two characters */ > || '-' || > case /* Prefix month with 0 if short */ > when substr( Date, -5, 1 ) = '/' > then '0' > else '' > end > || > case /* Month = from after / to 4th last character */ > when substr( Date, 2, 1) = '/' > then substr( Date, 3, length( Date ) - 5 ) > else substr( Date, 4, length( Date ) - 6 ) > end > || '-' || > case /* Day = from 1st to character before first / */ > when substr( Date, 2, 1 ) = '/' > then '0' || substr( Date, 1, 1 ) /* Prefix with 0 if short */ > else substr( Date, 1, 2 ) > end > as Date >from Raw >; > >which correctly gives: > >2007-11-02 >1968-08-02 > >But is there a more robust, built in method? > >Thanks, >Tom Often there are data cleaning and normalizing tasks to be performed before the data is ready for (read: can be piped into) the database. I would preprocess the data with an awk script. Perl or PHP or many other scripting tools are also suitable. Although it can be done, SQL just isn't the most elegant language to do these kind of things. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Converting date from d/m/yy format
Several years ago when I worked for a Fortune 70 company, we had a server whose source code and database were complicated by timestamps. I say complicated because there were different timestamp datatypes used for different fields (inherited from the data sources), the data could be stored in different formats, timestamp precision varied, and it was a problem to translate back and forth between the different representations and compare them. All of this added up to the occasional exercise of some obscure bugs in the server. I successfully undertook a project to fix this for all time. My solution was very simple: all timestamps were represented as strings in the following format: "MMDD:HHmmSS.nn" This format, no what the original data source or format, became the standard format for timestamps on this particular server. Precision was to the microsecond for all data, even if represented by zeroes. This had several virtues: > When debugging software, all timestamps were readable when using Debug. Instead of looking at some binary number, the timestamp was easily human readable. > When using administrative tools to access the database, it was easy to examine, modify, and compare timestamps, since they were all human readable and in exactly the same format. > When comparing timestamps in the software to determine the most current, a simple string comparison always produced the correct result. The only feature that might have been needed (but wasn't on this particular server) was the ability to add or subtract time intervals from the timestamp. You may wish to consider a similar approach to managing your date and time information. Lee Crain -Original Message- From: T&B [mailto:[EMAIL PROTECTED] Sent: Friday, November 02, 2007 12:29 AM To: sqlite-users@sqlite.org Subject: [sqlite] Converting date from d/m/yy format Hi all, How can I convert dates from the format d/m/yy to SQL style YYY-MM-DD? I have some imported data that includes a date column in the format d/ m/yy, where: d = day as 1 or two digits m = month as 1 or two digits yy = year as two digits eg: 2/11/07 = today 2/8/68 = 2nd of August, 1968 How can I convert this in SQLite to -MM-DD? The data is from a bank, so I have no control over its production. I couldn't find any suitable built in SQLite functions, which all seem to operate in the other direction. The best I've come up with so far is: create table Raw( Date ); insert into Raw( Date ) values ( '2/11/07' ); insert into Raw( Date ) values ( '2/8/68' ); select case /* Prefix for year = 20 if year starts 0, 1 or 2, else 19 */ when cast( substr( Date, -2, 1 ) as integer ) < 3 then '20' else '19' end || substr( Date, -2, 2 ) /* Year = last two characters */ || '-' || case /* Prefix month with 0 if short */ when substr( Date, -5, 1 ) = '/' then '0' else '' end || case /* Month = from after / to 4th last character */ when substr( Date, 2, 1) = '/' then substr( Date, 3, length( Date ) - 5 ) else substr( Date, 4, length( Date ) - 6 ) end || '-' || case /* Day = from 1st to character before first / */ when substr( Date, 2, 1 ) = '/' then '0' || substr( Date, 1, 1 ) /* Prefix with 0 if short */ else substr( Date, 1, 2 ) end as Date from Raw ; which correctly gives: 2007-11-02 1968-08-02 But is there a more robust, built in method? Thanks, Tom -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Converting date from d/m/yy format
T&B wrote: Hi John, How can I convert dates from the format d/m/yy to SQL style YYY-MM- DD? The data is from a bank, so I have no control over its production. I couldn't find any suitable built in SQLite functions, which all seem to operate in the other direction. If you transform the date into the internal Sqlite format on data aquisition. See above re "no control over its production". I have to convert the dates. I can and want to do it in SQLite, since I won't have to introduce another architecture. Then you can use the internal Sqlite date functions to present the date in the format of your choice. See above re "other direction", which is not what I need. Thanks, Tom If you are getting data and storing it in an Sqlite database why not store it in the appropriate format? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Converting date from d/m/yy format
Hi Gerry, I would suggest either [scan] or [regexp] , with the former probably being easier. I'd love to use RegExp, but SQLite doesn't include it in its standard functions (though I wish it did for so many reasons). I'm not familiar with scan. Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Converting date from d/m/yy format
Hi John, How can I convert dates from the format d/m/yy to SQL style YYY-MM- DD? The data is from a bank, so I have no control over its production. I couldn't find any suitable built in SQLite functions, which all seem to operate in the other direction. If you transform the date into the internal Sqlite format on data aquisition. See above re "no control over its production". I have to convert the dates. I can and want to do it in SQLite, since I won't have to introduce another architecture. Then you can use the internal Sqlite date functions to present the date in the format of your choice. See above re "other direction", which is not what I need. Thanks, Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Converting date from d/m/yy format
T&B wrote: Hi all, How can I convert dates from the format d/m/yy to SQL style YYY-MM-DD? I have some imported data that includes a date column in the format d/ m/yy, where: d = day as 1 or two digits m = month as 1 or two digits yy = year as two digits eg: 2/11/07 = today 2/8/68 = 2nd of August, 1968 How can I convert this in SQLite to -MM-DD? The data is from a bank, so I have no control over its production. I couldn't find any suitable built in SQLite functions, which all seem to operate in the other direction. The best I've come up with so far is: create table Raw( Date ); insert into Raw( Date ) values ( '2/11/07' ); insert into Raw( Date ) values ( '2/8/68' ); select case /* Prefix for year = 20 if year starts 0, 1 or 2, else 19 */ when cast( substr( Date, -2, 1 ) as integer ) < 3 then '20' else '19' end || substr( Date, -2, 2 ) /* Year = last two characters */ || '-' || case /* Prefix month with 0 if short */ when substr( Date, -5, 1 ) = '/' then '0' else '' end || case /* Month = from after / to 4th last character */ when substr( Date, 2, 1) = '/' then substr( Date, 3, length( Date ) - 5 ) else substr( Date, 4, length( Date ) - 6 ) end || '-' || case /* Day = from 1st to character before first / */ when substr( Date, 2, 1 ) = '/' then '0' || substr( Date, 1, 1 ) /* Prefix with 0 if short */ else substr( Date, 1, 2 ) end as Date from Raw ; which correctly gives: 2007-11-02 1968-08-02 But is there a more robust, built in method? Thanks, Tom If you transform the date into the internal Sqlite format on data aquisition. Then you can use the internal Sqlite date functions to present the date in the format of your choice. Physically Sqlite stores a date as a floating point Julian date based on an astronomical epoch. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Converting date from d/m/yy format
T&B wrote: Hi all, How can I convert dates from the format d/m/yy to SQL style YYY-MM-DD? I would suggest either [scan] or [regexp] , with the former probably being easier. Gerry - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Converting date from d/m/yy format
Hi all, How can I convert dates from the format d/m/yy to SQL style YYY-MM-DD? I have some imported data that includes a date column in the format d/ m/yy, where: d = day as 1 or two digits m = month as 1 or two digits yy = year as two digits eg: 2/11/07 = today 2/8/68 = 2nd of August, 1968 How can I convert this in SQLite to -MM-DD? The data is from a bank, so I have no control over its production. I couldn't find any suitable built in SQLite functions, which all seem to operate in the other direction. The best I've come up with so far is: create table Raw( Date ); insert into Raw( Date ) values ( '2/11/07' ); insert into Raw( Date ) values ( '2/8/68' ); select case /* Prefix for year = 20 if year starts 0, 1 or 2, else 19 */ when cast( substr( Date, -2, 1 ) as integer ) < 3 then '20' else '19' end || substr( Date, -2, 2 ) /* Year = last two characters */ || '-' || case /* Prefix month with 0 if short */ when substr( Date, -5, 1 ) = '/' then '0' else '' end || case /* Month = from after / to 4th last character */ when substr( Date, 2, 1) = '/' then substr( Date, 3, length( Date ) - 5 ) else substr( Date, 4, length( Date ) - 6 ) end || '-' || case /* Day = from 1st to character before first / */ when substr( Date, 2, 1 ) = '/' then '0' || substr( Date, 1, 1 ) /* Prefix with 0 if short */ else substr( Date, 1, 2 ) end as Date from Raw ; which correctly gives: 2007-11-02 1968-08-02 But is there a more robust, built in method? Thanks, Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
