Northwind example database

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Northwind example database

GeoffW
Hi

Just for educational purposes I have been experimenting a little with the Northwind Sqlite database contained on the sqlite official site.
Download link: http://download.vive.net/Northwind.zip.

Am I misunderstanding here or are the dates in the wrong format for sqlite within this converted database ?

Assuming it is wrong and not my understsanding, are there any easy ways to get the dates reversed and corrected to sqlite order and written back out to the database ?

Thanks

Geoff
Reply | Threaded
Open this post in threaded view
|

Re: Northwind example database

Simon Slavin-3

On 27 Mar 2010, at 10:46am, GeoffW wrote:

> Just for educational purposes I have been experimenting a little with the
> Northwind Sqlite database contained on the sqlite official site.
> Download link: http://download.vive.net/Northwind.zip.
>
> Am I misunderstanding here or are the dates in the wrong format for sqlite
> within this converted database ?

If you want to use SQLite's date and time functions, you're right.

> Assuming it is wrong and not my understsanding, are there any easy ways to
> get the dates reversed and corrected to sqlite order and written back out to
> the database ?

Fastest to do it with a spreadsheet.

UPDATE employees SET birthdate='1948-12-08',hiredate='1992-05-01' WHERE employeeID=1;
UPDATE employees SET birthdate='1952-02-19',hiredate='1992-08-14' WHERE employeeID=2;
UPDATE employees SET birthdate='1963-08-30',hiredate='1992-04-01' WHERE employeeID=3;
UPDATE employees SET birthdate='1937-09-19',hiredate='1993-05-03' WHERE employeeID=4;
UPDATE employees SET birthdate='1955-03-04',hiredate='1993-10-17' WHERE employeeID=5;
UPDATE employees SET birthdate='1963-07-02',hiredate='1993-10-17' WHERE employeeID=6;
UPDATE employees SET birthdate='1960-05-29',hiredate='1994-01-02' WHERE employeeID=7;
UPDATE employees SET birthdate='1958-01-09',hiredate='1994-03-05' WHERE employeeID=8;
UPDATE employees SET birthdate='1966-01-27',hiredate='1994-11-15' WHERE employeeID=9;

Knock yourself out.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Northwind example database

Griggs, Donald-3
 

On 27 Mar 2010, at 10:46am, GeoffW wrote:

> Just for educational purposes I have been experimenting a little with
> the Northwind Sqlite database contained on the sqlite official site.
> Download link: http://download.vive.net/Northwind.zip.
>
> Am I misunderstanding here or are the dates in the wrong format for
> sqlite within this converted database ?

> Assuming it is wrong and not my understsanding, are there any easy
> ways to get the dates reversed and corrected to sqlite order and
> written back out to the database ?


Perhaps this is better performed in the calling language, but the following sql should reformat these dates.  Of course, you'd need to substitute and repeat for the other fields.


-- Reformat date from, e.g., '1/5/2010 12:00:00 AM'  to  '2010-01-05'

update employees
  set birthdate = replace (birthdate, ' 12:00:00 AM', '');

update employees
  set birthdate = '0' || birthdate
  where substr(birthdate, 2,1) == '/';
 
  update employees
  set birthdate =
       substr(birthdate, 1, 3)
    || '0'
    || substr(birthdate, 4,99)
  where substr(birthdate, 5,1) == '/';
 
-- Date should now be formatted as dd/mm/yyyy
-- Now change to yyyy-mm-dd

update employees
  set birthdate =
         substr(birthdate, 7,4)
      || '-'
      || substr(birthdate, 1,2)
      || '-'
      || substr(birthdate, 4,2);
   
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Northwind example database

Gabor Grothendieck
It would be nice if SQLite had a strptime-like function for things
like this and not just strftime.

On Mon, Mar 29, 2010 at 10:13 AM, Griggs, Donald
<[hidden email]> wrote:

>
>
> On 27 Mar 2010, at 10:46am, GeoffW wrote:
>
>> Just for educational purposes I have been experimenting a little with
>> the Northwind Sqlite database contained on the sqlite official site.
>> Download link: http://download.vive.net/Northwind.zip.
>>
>> Am I misunderstanding here or are the dates in the wrong format for
>> sqlite within this converted database ?
>
>> Assuming it is wrong and not my understsanding, are there any easy
>> ways to get the dates reversed and corrected to sqlite order and
>> written back out to the database ?
>
>
> Perhaps this is better performed in the calling language, but the following sql should reformat these dates.  Of course, you'd need to substitute and repeat for the other fields.
>
>
> -- Reformat date from, e.g., '1/5/2010 12:00:00 AM'  to  '2010-01-05'
>
> update employees
>  set birthdate = replace (birthdate, ' 12:00:00 AM', '');
>
> update employees
>  set birthdate = '0' || birthdate
>  where substr(birthdate, 2,1) == '/';
>
>  update employees
>  set birthdate =
>       substr(birthdate, 1, 3)
>    || '0'
>    || substr(birthdate, 4,99)
>  where substr(birthdate, 5,1) == '/';
>
> -- Date should now be formatted as dd/mm/yyyy
> -- Now change to yyyy-mm-dd
>
> update employees
>  set birthdate =
>         substr(birthdate, 7,4)
>      || '-'
>      || substr(birthdate, 1,2)
>      || '-'
>      || substr(birthdate, 4,2);
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users