(SELECT CASE WHEN CAST(SUBSTR( date_field,1,2) AS INTEGER) >= 10 THEN SUBSTR( date_field,1,2) ELSE '0'||ĬAST(SUBSTR ( date_field,1,2) AS INTEGER) END ) ||'-'|| (SELECT CASE WHEN CAST(SUBSTR( date_field,-7,3) AS INTEGER)>=10 THEN SUBSTR( date_field,-7,2) ELSE '0'|| CAST(SUBSTR( date_field,-6,2) AS INTEGER) END)
ICONOMY 8 AND SQLITE UPDATE
UPDATE table_name SET date_field = (select substr( date_field,-4,4)) ||'-'|| Sqlite> SELECT order_num,(SELECT SUBSTR(order_date,-4,4)) ||'-'||(SELECT CASE WHEN CAST(SUBSTR(order_date,1,2) AS INTEGER)>=10 THEN SUBSTR(order_date,1,2) ELSE '0'||ĬAST(SUBSTR (order_date,1,2) AS INTEGER) END ) ||'-'|| (SELECT CASE WHEN CAST(SUBSTR(order_date,-7,3) AS INTEGER)>=10 THEN SUBSTR(order_date,-7,2) ELSE '0'|| CAST(SUBSTR(order_date,-6,2) AS INTEGER) END) AS 'order_date', order_stat,cust_id FROM order_list THEN SUBSTR( date_field,-7,2) ELSE '0'|| CAST(SUBSTR( date_field,-6,2) THEN SUBSTR( date_field,1,2) ELSE '0'|| CAST(SUBSTR ( date_field,1,2)ĪS INTEGER) END ) ||'-'|| (SELECT CASE WHEN CAST(SUBSTR( date_field,-7,3) AS INTEGER)>=10 ||'-'||(SELECT CASE WHEN CAST(SUBSTR( date_field,1,2) AS INTEGER)>=10 Using SELECT CASE and the CAST( substring AS INTEGER) function makes it possible to extract the value of the month and the day. Unfortunately in the example above, some of the values in the date column have only one digit for the month and or day. Ideally the dates in the column would have two digits for the month, two for the day and 4 digits for the year, in which case it would be a simple matter of using the SUBSTR function toĮxtract the elements and rearrange them as shown below. Sqlite> SELECT order_num,order_date,order_stat,cust_id FROM order_list Our objective is to change it to "yyyy-mm-dd" format. Here we have a table listing dates in the conventional American format of Month, Day, Year separated by "/". Reformat Date Strings from mm/dd/yyyy to the Standard SQLite Datestring Format of yyyy-mm-dd Using SELECT CASE > END) AS 'Overtime Hours' FROM tbl_employee, tbl_Hours WHERE tbl_employee.employee_id = tbl_hours.employee_id GROUP BY tbl_hours.week_number
Sqlite> SELECT tbl_hours.week_number 'Week #',SUM(hours) AS 'Payroll Hours' ,SUM(CASE WHEN (hours>40) THEN hours-40 The next example uses the CASE clause in an aggregate query to list the total overtime incurred by all employees for each week. Tbl_employee.first_name||' ' ||tbl_employee.last_name 'Name', hours,CASE WHEN (hours>40) THEN hours-40 ELSE 0ĮND AS overtime FROM tbl_employee, tbl_Hours WHERE tbl_employee.employee_id = tbl_hours.employee_id ORDER BY tbl_hours.week_number,tbl_employee.employee_id Sqlite> SELECT tbl_hours.week_number 'Week #', tbl_employee.employee_id 'Id Number', Subtract 40 from the hours worked to calculate the amount of overtime worked, otherwise list zero in the overtime column.
The CASE expression uses the condition that if the number of hours worked in a week by a given employee is greater than 40 then In the following example we want to list the number of hours worked each week by the employees and to calculate any overtime that they may have worked. > WHEN state='DC' THEN 'District of Columbia' WHEN third conditional expression THEN column valueĬASE WHEN conditional expression THEN column value WHEN second conditional expression THEN column value CASE WHEN first conditional expression THEN column value
0 kommentar(er)
