SQL Date transformations
I've received data in Excel and the DATE column (named ALL_PERIODS) is STRING and has this format:
JFM 14 - W/E 04/05/14
January, February, March 2014, Week end 04/05/14
I need to divide this column into three new columns: DATE_ (DATE), YEAR_ (INT), QUARTER_ (INT), so I've written these transformations:
ALTER TABLE MY_TABLE ADD DATE_TEMP VARCHAR2 (128); UPDATE MY_TABLE SET DATE_TEMP = SUBSTR(ALL_PERIODS, -2, 2); UPDATE MY_TABLE SET DATE_TEMP = REPLACE(DATE_TEMP, '/', ''); UPDATE MY_TABLE SET DATE_TEMP = to_char(to_date(DATE_TEMP, 'mmddyyyy'), 'yyyy-mm-dd'); UPDATE MY_TABLE SET DATE_TEMP = REPLACE(DATE_TEMP, '00', '20'); UPDATE MY_TABLE SET DATE_TEMP = TO_DATE(DATE_TEMP, 'YYYY-MM-DD'); ALTER TABLE MY_TABLE ADD DATE_ DATE; UPDATE MY_TABLE SET DATE_ = DATE_TEMP; ALTER TABLE MY_TABLE DROP COLUMN DATE_TEMP;
ALTER TABLE MY_TABLE ADD YEAR_TEMP VARCHAR2(128 BYTE); UPDATE MY_TABLE SET YEAR_TEMP = SUBSTR(ALL_PERIODS, 8, 4); ALTER TABLE MY_TABLE ADD YEAR_ NUMBER(4); UPDATE MY_TABLE SET YEAR_ = YEAR_TEMP; UPDATE MY_TABLE SET YEAR_ = CONCAT('20', YEAR_); ALTER TABLE MY_TABLE DROP COLUMN YEAR_TEMP;
ALTER TABLE MY_TABLE ADD QUARTER NUMBER(1); UPDATE MY_TABLE SET QUARTER_ = 4 WHERE DATE_ = '30-DEC-17'; ...and the same with other 3 quarters
It somehow works, but I guess it is not effective (I'm not very advanced in SQL and I'm still learning), and the update operations are too slow, so I'd like to transform the ALL_PERIODS column while inserting the data into new table with the three needed columns.
Could you please tell me, how should the INSERT look like? Thank you N.
I started off with simply inserting records to emulate your Excel date string. You can use this logic to see how the transformations can be done.
SETUPINSERT INTO myTable (baseDate) SELECT 'JFM 14 - W/E 04/05/14' FROM dual UNION ALL SELECT 'AMJ 14 - W/E 06/05/14' FROM dual UNION ALL SELECT 'JAS 14 - W/E 09/05/14' FROM dual UNION ALL SELECT 'OND 14 - W/E 12/05/14' FROM dual UNION ALL SELECT 'JFM 15 - W/E 04/05/15' FROM dual UNION ALL SELECT 'AMJ 15 - W/E 06/05/14' FROM dual UNION ALL SELECT 'JAS 15 - W/E 09/05/14' FROM dual UNION ALL SELECT 'JAS 49 - W/E 03/05/49' FROM dual UNION ALL SELECT 'JAS 50 - W/E 04/05/50' FROM dual
SELECT row verifies the default date format for your server. This value could affect the date interpretations of your string.
SELECT value FROM v$nls_parameters WHERE parameter ='NLS_DATE_FORMAT';| VALUE | | :-------- | | DD-MON-RR |
After I have the values inserted, I can just update my table based on that value.
UPDATE myTable SET DATE_ = TO_DATE( SUBSTR(baseDate,INSTR(baseDate, ' ', -1)+1,8) -- Find last space to get end of string date value. , 'MM/DD/RR' ) /* TO_DATE already creates date object. RR mask rounds to 4-digit year. (49 = 1949, 50 = 2050) */ , QUARTER_ = CASE SUBSTR(baseDate, 1, 3) WHEN 'JFM' THEN 1 WHEN 'AMJ' THEN 2 WHEN 'JAS' THEN 3 WHEN 'OND' THEN 4 END
DATE_, you don't need to do a
TO_DATE will convert your string to a date value for the date column in the db. Your database doesn't really care if a date looks like 'yyyy-mm-dd', because it doesn't see it that way anyway. I used the
RR mask to convert the 2-digit year value to a 4-digit year value. This will translate
1950. If you don't have any need for
19xx dates, you can just use
YY. If you need to, you can do additional validation on your string value to ensure it can be converted to a date.
QUARTER_, it's just a simple
CASE statement to interpret your quarter string to a number.
Then, I update
YEAR_ based off of the date value in
DATE_. This ensures that my year stays consistent within the row. It can also be done up in the initial update if the value is extracted from the base string.
UPDATE myTable SET YEAR_ = EXTRACT ( YEAR FROM DATE_ )
This gives us:
BASEDATE | DATE_ | YEAR_ | QUARTER_ :-------------------- | :-------- | ----: | -------: JFM 14 - W/E 04/05/14 | 05-APR-14 | 2014 | 1 AMJ 14 - W/E 06/05/14 | 05-JUN-14 | 2014 | 2 JAS 14 - W/E 09/05/14 | 05-SEP-14 | 2014 | 3 OND 14 - W/E 12/05/14 | 05-DEC-14 | 2014 | 4 JFM 15 - W/E 04/05/15 | 05-APR-15 | 2015 | 1 AMJ 15 - W/E 06/05/14 | 05-JUN-14 | 2014 | 2 JAS 15 - W/E 09/05/14 | 05-SEP-14 | 2014 | 3 JAS 49 - W/E 03/05/49 | 05-MAR-49 | 2049 | 3 JAS 50 - W/E 04/05/50 | 05-APR-50 | 1950 | 3
DATE_ is an actual date object, on display, you can format it with whatever mask you want.
Format SQL Server Dates with FORMAT Function, I started off with simply inserting records to emulate your Excel date string. You can use this logic to see how the transformations can be done. I hope you enjoyed this tutorial on data cleaning and transformation with SQL. This is just the beginning of what you can use SQL for in data analysis. If you’d like to learn more, Next Tech’s SQL for Data Analysis course covers: More functions used for data preparation and cleaning; Aggregate functions and window functions
INSERT INTO my_table (COL1, COL2, ... DATE_, QUARTER_, YEAR_) SELECT sr.COL1, sr.COL2, .... TO_DATE(TO_CHAR(TO_DATE(SUBSTR(sr.ALL_PERIODS, -8), 'mm/dd/yy'), 'YYYY-MM-DD'), 'YYYY-MM-DD') DATE_, CASE SUBSTR(sr.ALL_PERIODS, 1, 3) WHEN 'JFM' THEN 1 WHEN 'AMJ' THEN 2 WHEN 'JAS' THEN 3 WHEN 'OND' THEN 4 END QUARTER_, '20'||SUBSTR(sr.ALL_PERIODS, 5,2) YEAR_, FROM SOURCE_TABLE sr [WHERE sr.COL99 = 'Y'];
SQL Date transformations, In SQL Server, we have used built-in functions such as SQL GETDATE() and GetUTCDate() to provide server date and format in various formats Data Types for Date and Time We have the following SQL convert date and Time data types in SQL Server. In SQL Server, we have used built-in functions such as SQL GETDATE () and GetUTCDate () to provide server date and format in various formats. SYSDATETIME (): To returns the server’s date and time
The DDL-Statements (add/delete column) are slow and unnecessary. You should not change the table definition when inserting data!
I would insert the 3 columns like this (replace the date string i used with the real ALL_PERIODS value):
INSERT INTO MY_TABLE (DATE_, YEAR_, QUARTER_) TO_DATE(SUBSTR('JFM 14 - W/E 04/05/14', -8), 'dd/mm/yy'), EXTRACT(YEAR FROM TO_DATE(SUBSTR('JFM 14 - W/E 04/05/14', -2), 'yy')), -- OR SHORTER IF ALWAYS IN THE 2000: TO_NUMBER(SUBSTR('JFM 14 - W/E 04/05/14', -2)) + 2000 DECODE(SUBSTR('JFM 14 - W/E 04/05/14',1,3), 'JFM', 1, 'AMJ', 2, 'JAS', 3, 'OND', 4);
SQL Convert Date functions and formats, SQL Server comes with the following data types for storing a date or a date/time value in the database: DATE - format YYYY-MM-DD; DATETIME - format: YYYY- Change a Date Format Using ActiveX and SQL DTS Transformations 1 min 1,253 January 11, 2018 by Rudy Let’s say you are importing data from a flat file to SQL Server via DTS.
If ALL_PERIODS is already loaded into the table? And the extra columns to calculate are already added to the table?
Then 1 update should suffice.
UPDATE MY_TABLE SET DATE_ = TO_DATE(SUBSTR(RTRIM(ALL_PERIODS),-8),'MM/DD/YY') ,YEAR_ = 2000+SUBSTR(ALL_PERIODS,5,2) ,QUARTER_ = DECODE(SUBSTR(ALL_PERIODS,1,3), 'JFM', 1, 'AMJ', 2, 'JAS', 3, 'OND', 4) WHERE ALL_PERIODS IS NOT NULL AND DATE_ IS NULL
Test on db<>fiddle here
Date Functions in SQL Server and MySQL, The format used to convert between data types, such as a date or string format. Can be one of the following values: Converting datetime to Starting with SQL Server 2012, a new function to handle formatting dates was introduced which is similar to Oracle's to_date function. Many Oracle DBAs complained about the SQL Server CONVERT function and its poor flexibility and now we have a new way to format dates in SQL Server.
SQL Server CONVERT() Function, Date Format in SQL is utilized for formatting date. Learn how to get different SQL date formats along with CONVERT function. Convert Datetime to Date Summary: in this tutorial, you will learn how to convert a datetime to a DATE by using the CONVERT (), TRY_CONVERT (), and CAST () functions. To convert a datetime to a date, you can use the CONVERT (), TRY_CONVERT (), or CAST () function. Convert datetime to date using the CONVERT () function
Date Format in SQL, This lesson of the SQL tutorial for data analysis covers how dates and times are formatted in SQL, and best practices for interacting with them. Vijay Bhaskar 3/13/2012 14 Comments. SQL Transformation is a connected transformation used to process SQL queries in the midstream of a pipeline. We can insert, update, delete and retrieve rows from the database at run time using the SQL transformation. The SQL transformation processes external SQL scripts or SQL queries created in the SQL editor.
SQL Date Format, SQL Server string to date / datetime conversion - datetime string format sql server. -- MSSQL string to datetime conversion - convert char to date - convert varchar Transformations are PL/SQL functions, procedures, packages, and types that enable you to transform data. You use transformations when designing mappings and process flows that define ETL processes. Transformations are stored in the Warehouse Builder workspace and can be used in the project in which they are defined.