SQL Date transformations

SQL Date transformations

sql format date
sql date format yyyymmdd
sql convert string to date dd/mm/yyyy
sql convert string to date yyyymmdd
sql date comparison
sql date format dd-mmm-yyyy
sql cast date
sql server convert(datetime to date)

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:

Column DATE:

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;

Column YEAR:

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;

Column Quarter:

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.

SETUP

INSERT 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

This next 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

For DATE_, you don't need to do a TO_CHAR since 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 49 AS 2049 and 50 as 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.

For 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

Since DATE_ is an actual date object, on display, you can format it with whatever mask you want.

db<>fiddle here

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);

Regards

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.