Returns the day of the week for the specified date. 2. 0 SPS 06 ;. 0 SPS 00 ; 1. SAP HANA SQL Reference Guide for SAP HANA Platform. 0 SPS 07 ; 2. Calculation View). The goal is to calculate. Further information: Security, Authorization and Licensing, SAP Note: 1899480 How to handle HANA Alert 31: 'License. Tax, both direct and indirect, is also an area where legal regulations and the mandatory changes have a high impact and where technology would need to quickly adapt in order to continue providing viable solutions. You may choose to manage your own preferences. Ideally, there is a way to only extract changed records, for example, using a change date column. Calculated column (column VALUE contains the long date):The following example returns the value 2678400 as the seconds between the two specified dates: SELECT SECONDS_BETWEEN ('2009-12-05', '2010-01-05') "seconds between" FROM DUMMY;SAP HANA Date and time functions || SAP HANA Studio || SQL FunctionsYou can see more SAP learning videos at our YouTube channel:HANA SQL Reference Guide for SAP HANA Platform. Datetime types . Display default format for timestamps in SAP HANA Studio. SELECT CAST (10. The usage of local timestamps is discouraged; use UTC times instead. In the SAP HANA database, each data type can be classified by its characteristics as follows: Classification of data types; Classification. The TIMESTAMP data type consists of date and time information. For a better understanding I will explain the functionality with an example. But I only want "202001" as my final output. 000 for time. 5. On the other hand, if you want to provide a date value as text, this value needs to be converted to a date value. The result is a signed integer value equal to (date-expression-2 - date-expression-1), in date parts. 12. An empty date (0000-00-00) is a special value in SAP HANA. Returns the current local system date. UPDATE T SET VAL = T2. Tools to detect potential functional and performance issues when migrating to SAP HANA: code inspector and ABAP test cockpit, ABAP trace and ABAP profiler, SQL trace. The following data types can be converted to NVARCHAR using the TO_NVARCHAR function: ALPHANUM, BIGINT,. SQL - SAP HANA - Use only first entry in. You may change to this: SELECT range, COUNT (*) AS vehicle_count FROM ( SELECT (CASE WHEN meter >= 0 AND meter< 10000 THEN '0-10' WHEN meter >= 10000 AND meter < 20000 THEN '10-20' WHEN meter >= 20000 AND meter <30000 THEN '20-30' WHEN meter >= 30000 AND meter < 40000 THEN '30-40'. 32 – Customers With Missing Credit Data F. 10269 Views. Binary. 5) "to int" FROM DUMMY;To ensure the security of SAP HANA, it's important that you keep your systems up to date by installing the latest SAP HANA revision and monitoring SAP security notes. Date, Time, and Timestamp each have the following prefixes: date'2010-01-01'I am trying to create an input parameter for 0FISCPER in a HANA calculation view where I want to populate the default value as past fiscal period for US calendar. The filters can be found by looking. 0 SPS 06 ; 2. At the end the resultAs I know the default format in HANA is YYYY-MM-DD and hence the output should appear like 2014-10-01. The range is between 0001-01-01 and 9999-12-31. YYYY-MM-DD. You can learn how to perform a currency conversion with SQLScript in our article "Currency conversion with SAP HANA SQLScript in transformation routines". 12. It returns a time dimension member based on another time dimension member defined as the reference date. For the exact expiration date, see the monitoring view M_LICENSE. because both functions return date values. Large Objects. TIMESTAMP : 4. Version 1. Hello. RSS Feed. 0 SPS 02 ; 2. SAP HANA SQL Reference Guide for SAP HANA Platform. Sorted by: 4. With this view set up, and likely materialised in a production environment, it is only a small additional step to create a WORKDAYS_BETWEEN function that mimics the SAP HANA equivalent: create function workdays_between (factory_calendar_id varchar (2), start_date date, end_date date ) RETURNS INTEGER AS $$ SELECT count (*) FROM. Take a. SAP HANA Cloud provides a single place to access, store, and process all enterprise data in real time. To follow the steps in this blog you need to be familiar with the earlier blog SAP AI Core –. If no value is provided the current. The portions that the function can return are the year, the month, the day of the year, the day of the month, the day of the week, the hour, the minute, or the second. This site uses cookies and related technologies, as described in our privacy statement , for purposes that may include site operation, analytics, enhanced user experience, or. 5 to the INTEGER value 10, truncating the mantissa. 0 SPS 02. Viewed 5k times. The SAP HANA 2. With the first release of SAP HANA Cloud in 2023 users are now able to migrate their on-premises SAP HANA database to SAP HANA Cloud using the migration self-service. I was hoping I will be able to use. This approach is intended for small datasets only:. Boolean. 0 SPS 06 offers new features to further reduce total cost-of-ownership (TCO) as well as security and administration capabilities for improved database operations, data management, data integration, application development, and smart multi-model capabilities. hana日期函数. Important Disclaimer for Features in SAP HANA Platform, Options and Capabilities DAYDATE. To ensure a smooth migration, start by assessing what needs to be transferred and creating a plan for the migration. e. 0 SPS 03 ; 2. This site uses cookies and related technologies, as described in our privacy statement, for purposes that may include site operation,. 2 Answers. The SAP HANA options and capabilities listed below are available in connection with the platform and enterprise editions of SAP HANA, depending on the software license used. 0. If format-style is. The DATE data type consists of year, month, and day information to represent a date value. To compute the date so that the output date is set to the last day of the month when the input date is the last day of the month, use the ADD_MONTHS_LAST function. 0: version for SAP HANA- this was the first version of SAP Business Suite on HANA. you have an import model; or BW Live model; or HANA LIVE model; you used a date dimension or non-date dimension to calculate the accumulated sum; In this case, you can use “add calculation” from your target measure column to achieve multiple types of. Convert date to SAP HANA DATE format with TO_DATE. AFRU. In order to understand the working of HANA and its delta merge concepts, it is important. 0 SPS 01 ; 2. I want to convert to YYYYMMDD format in table. TO_DATE Function (Data Type Conversion) TO_DATS Function (Data Type Conversion) TO_DECIMAL Function (Data Type Conversion)Description. Datetime Functions. On the other hand, if you want to provide a date value as text, this value needs to be converted to a date value. You may choose to manage your own preferences. 0 SPS 07 ; 2. Description: Converts a specified date from a field to a text string. 3. <format>. And lastly, it’s possible to use HANA’s date functions to determine the number of years, months, days, seconds, or even nanoseconds between two dates. If I define the target field as VARCHAR the load works fine. Sorted by: 4. The SAP HANA database supports two kinds of license keys: temporary license keys and permanent license keys. 0 SPS03 (opens in a new tab)”>2573900 – Changed Behavior of the SQL function DAYS_BETWEEN(<d1>,<d2>) in HANA 2. 1. You may choose to manage your own preferences. TO_DATE Function (Data Type Conversion) Date Functions. 江边蚊子 关注 赞赏支持. SAP HANA SQL how to convert Date and Time to timestamp. 0 SP7? if not today, when it is expected to be available. Global licenses are for the system database and all the tenant databases, but a license installed in a tenant. Also, End of Month (EOM) is a property I had maintained in the Time dimension and has been fetched into table function. 0 SPS 01 ;Date and time functions perform operations on date and time data types or return date or time information. SAP HANA SQL Reference Guide for SAP HANA Platform. Open the SAP HANA Database connector. SAP HANA SQL Reference Guide for SAP HANA Platform. (it is workin tested. So I'm trying to get the Monday and Sunday from the 35th week of 2021. 0 SPS 01 ;. Using the TO_DATE function to normalise the calculation to full days is the workaround here, too. <date_2> Specifies the ending TIMESTAMP for the comparison. Understood. 0 SPS 07. Even if the format is not listed in the current documentation, you can use a statement like. For example, ADD_MONTHS_LAST(DATE) returns a date, while ADD_MONTHS_LAST(TIMESTAMP) returns a timestamp. SAP HANA Cloud; Data management. because both functions return date values. SAP HANA SQL Reference Guide for SAP HANA Platform. Let. The key date for the currency translation is. On this page. The following example converts the value 2009-12-31 to a date value with the format YYYY/MM/DD. 0 SPS 06 ; 2. I am having a problem working with a particular day of the year, the 2nd Saturday of May. CREATE COLLECTION C1; INSERT INTO C1 VALUES( {A:1,B:2}); INSERT INTO C1 VALUES( {A:'ABC'}); SELECT. CREATE ROW TABLE T2 (KEY INT PRIMARY KEY, VAR INT); INSERT INTO T2 VALUES (1, 2); INSERT INTO T2 VALUES (3, 6); Update the values of table T by joining the target table T with table T2. int COMMENTARY_MASTER there are several timestamps. A new installation of SAP S/4HANA needs to run on the SAP HANA database. The complete list of new features is available in the What’s New in the SAP HANA Platform 2. SAP HANA SQL Reference Guide for SAP HANA Platform. 0 SPS 04 ; 2. 000 for time. This site uses cookies and related technologies,. Create the. Insert values into an array value construction by query. tzone INTO DATE calday . 1990,0:00 GMT * 10000 to a certain date. My task is to convert a ABAP style date (i. This document. KEY; SELECT * FROM T; KEY. by Johannes Klostermeier. The system checks the posting date and the related posting period twice: When you post an FI-CA document, the system checks whether the posting period belonging to the posting date is still open or whether the posting date is in the future (comparison date is the CPU date). ) 201703301. When a given set of operand/argument types does not match what an operator/function expects, a type conversion is carried out by the SAP HANA database. 0 • SAP Simple Finance, on-premise edition 1503 • SAP S/4HANA Finance 1605 Note. You may choose to manage your own preferences. The value 3652062 is the NULL value. 0 SPS 04 ; 2. The following example converts the value 2010-01-12 to the ABAP DATE string 20100112. The standard format of this data type is YYYY-MM-DD. 0 SPS 07. Even though an empty date looks like a NULL or unknown value, it is not. More Information. It also satisfies a IS NOT NULL predicate. I am not sure if you can use CURRENT_DATE in the calculated attributes. The column-oriented in-memory database design allows you to run advanced analytics alongside high-speed transactions –. 2. 820. The use of local times or conversion between local time zones might require additional handling in. . I also use following Hana functions that can be found in this guide. SAP HANA licenses can be installed for the system database (global) or for a single tenant database (local). FROM dummy. 681. Therefore, please use the TO_NVARCHAR function instead. 2. SAP HANA Service for SAP BTP in AWS and Google Cloud Regions; SAP HANA SQL Reference Guide for SAP HANA Platform; SQL Reference; SQL Functions; Alphabetical List Of Functions; CURRENT_DATE Function (Datetime) An empty date (0000-00-00) is a special value in SAP HANA. 2. Example. "UpdateDate" and "CreateDate" are stored as Date in SAP HANA, keeping year, month, day and 00:00:00. To overcome this type situation, we can use YTD, MTD, QTD concept in the HANA modelling. Date and time functions are scalar functions that perform an operation on a date and time input value and returns either a string, numeric, or date and time value. The following example increments the specified date value 2009-12-05 by 1 year, and returns the value 2010-12-05: SELECT ADD_YEARS (TO_DATE ('2009-12-05', 'YYYY-MM-DD'), 1) "add years" FROM DUMMY; Related Information So I now want to use this same query (achieving same results) in SAP HANA but there is no date trunc function and I also tried. 0 SPS 02 ; 2. When you use day as the date part, the DATEDIFF. Without the CAST function, the data type of the parameter is ambiguous. For example, the empty date can be represented as '', which behaves like an empty string. The data type DATE consists of year, month and day information to represent a date value. First of all extract October from the October 2012 string, for that you need to write function Left ("October 2012";Pos ("October 2012";" ")) and save it in a variable "ABC"--- this will give you October. The parameter <date> must be implicitly or explicitly converted to one of the following SQL data types: The SQL data. SAP HANA SQL Data Warehousing is actively promoted next to or together with BW. 0 SPS 04 ; 2. 1990) Any suggestions would be appreciated. While the function is called TO_VARCHAR, in SAP HANA, VARCHAR is an alias for the NVARCHAR data type. The editor that I use which is TOAD for SAP Solution give me a wrong date format when I execute the procedure. Regarding the documentation on this, you can check the HELP in HANA Studio and search for "Functions used in Expressions" and check the function. • Conversion. signum = '-' for subtract days. if the returned date is in the form of YYYYMMDD, I suggest you use the following line in LOAD Script: Date(Date# ('BUDAT','YYYYMMDD'),'YYYY-MM-DD') as New_Date. Browse to the Manage tab in your Azure Data Factory or Synapse workspace and select Linked Services, then click New: Azure Data Factory. The CONVERT function can be used to convert a string to a DATE, TIME, or TIMESTAMP data type, provided that there is no ambiguity when parsing the string. However, this does not always fly. Available Versions: QRC 3/2023 ; QRC 2/2023 ; QRC 1/2023 ; QRC 4/2022 * QRC 3/2022 * QRC 2/2022 *. 0 SPS 07. SAP HANA on-premise : SAP HANA file system : CSV : Target table can be created : Import from statement: SAP HANA Cloud, HANA database : data lake Files, S3, Azure, GCS, Alibaba OSS : CSV, Parquet, JSON (for document stores) Import from statement: SAP HANA on-premise : SAP HANA file system : CSV : Insert into table. You need at least three confirmed participants to register and SAP will. DATE: DATE: TIME: TIME (or TIMESTAMP, depending on which value you choose for the Map SQL Server Data Type Time to Timestamp remote source. YYYY stands for the year, MM for the month and DD for the day. 70 is the 29th of January 2021. This site uses cookies and related technologies, as described in our privacy statement, for purposes that may include site operation, analytics, enhanced user experience, or advertising. Create table T2, and insert two rows into it. The SAP HANA database supports two kinds of license keys: temporary license keys and permanent license keys. This site uses cookies and related technologies, as described in our privacy statement, for purposes that may include site operation, analytics, enhanced user experience, or advertising. Use the following statements to test the behavior of the empty date value:Order and Contract Management. 1、current_date--返回当前本地系统日期。. Filter I tried: gueltig_bis >= to_date(now()) Which results in an. 0 PM, depending on your date display settings):privacy statement, for purposes that may include site operation, analytics, enhanced user experience, or advertising. Requirement. Greetings, I want to get Year from the date field "BEDGA" or "ENDDA" (having date format as YYYY. Which one to use depends on how you want to handle tie values. In HANA SQL, this can be achieved using built-in functions such as. ECC Tcode: SAP S/4HANA: FD32: UKM_BP: VMK1: UKM_MY_DCDS. I get this error: Unsupported date/time used in imported data Modify the data and import again. e yyyy-mm-dd I want it in 07. 0. data : v_days type i. HANA SQL TIME FUNCTIONS. 0000000 UTC AD,. Boolean. The following example returns 2 for the months between the two dates:. 0181218000000000E+07. Both the WEEK and ISOWEEK functions return the week number for a specified date but the format of the result is quite different, and the two functions may handle the first week of. Example The following example returns the value 2010-01-31 (or another format like Jan 31, 2010 , depending on your date display settings):Using Scalar Functions. It also satisfies a. SAP documents this in SAP notes ,) in HANA 2. "CreateTS" and "UpdateTS" store just the time in format hhmmss as int. The following example converts the string 2010-01-12 to a DATE value with the format YYYY-MM-DD, and returns the value 2010-01-12 (or another format like Jan 12, 2010, depending on your date display settings): SELECT TO_DATE ('2010-01-12', 'YYYY-MM-DD') "to date" FROM DUMMY; Computes the specified date plus the specified number of days. So after you read this tiny article you should be able to select on DEC15 dates in a Hana DB. 0000000 and 9999-12-31 23:59:59. 0 SPS 00. Hi All, SAP HANA - How to find Last Logon date and time of User in SAP HANA? ThanksComputes the number of seconds between date arguments <date_1> and <date_2>, which is semantically equal to (<date_2> - <date_1>). HANA SQL TIME FUNCTIONS. Returns the day of the month for the specified date. Understood. Example. <date>. 0 SPS 02 ; 2. If the time-class DATETIME uses. If you are using SAP S/4HANA system then CDS is quite important for technical spoc. 11/02/2018 -> 43406. To use this value, import a timezone dataset (see SAP Note 1791342). Solutions for each case: If DocDate type is NVARCHAR 8 with format YYYYMMDD: select * from OPDN A where A. Returns the current local system date. You may choose to manage your own preferences. SAP HANA includes capabilities such as multimodel processing, database management and administration, application development, and data. And I have time zone off set. . Date and time functions are scalar functions that perform an operation on a date and time input value and returns either a string, numeric, or date and time value. Filters are expressions which limit the data that is retrieved. Example The following example increments the date value 2009-12-05 by 30 days, and returns the value 2010-01-04 : Page Not Found | SAP Help Portal. 5 AS INTEGER) "cast" FROM DUMMY;Support for holding a cursor over ROLLBACK or COMMIT_AND_ROLLBACK is available in SAP HANA servers beginning with SAP HANA 2. hana日期函数. TRX_DATE_TIME>=FROM_DATE and. . Use as reference the below syntax without stripping the time value. 0 SPS 07. SAP is committed to provide mainstream maintenance for Business Suite 7 — the release that a lot of our customers are still using — until the end. 0 SPS 02 ; 2. We are using SAP HANA 1. SAP HANA SQL Command Network Protocol Reference. 7. For example, you can just compare directly. Therefore, please use. The. Fortunately the dates are given in single quotes in a SQL query, hence this should not stop us to go ahead. Converts the date string <date> into an ABAP DATE string with format 'YYYYMMDD'. SAP Customer Experience (SAP CX): SAP’s flagship customer relationship. The standard format of this data type is YYYY-MM-DD. The maximum length of the concatenated string is 8,388,607. 1. Sales Order Management and Processing (SAP S/4HANA) Delivery Date Rule. Insert values into an array value construction by query. If <start_month> is not specified, then the first quarter is assumed to begin in January. . SAP S/4HANA is an enterprise resource planning (ERP) software package meant to cover all day-to-day processes of an enterprise (for example, order-to-cash, procure-to-pay, plan-to-product, and request-to-service) and core capabilities. May 6, 2021. Large Objects. The SAP application layer or workload middle-ware layer runs in virtual machines. Computes the specified date, plus or minus the specified number of days. Creating a Graphical Calculation View . Since. Even though an empty date looks like a NULL or unknown value, it is not. CREATE ROW TABLE T2 (KEY INT PRIMARY KEY, VAR INT); INSERT INTO T2 VALUES (1, 2); INSERT INTO T2 VALUES (3, 6); Update the values of table T by joining the target table T with table T2. databaseName <name> Specifies the name of the database to connect to. The to_char function supports the Oracle 9i timestamp data type up to 9 digits precision for sub-seconds. An empty date (0000-00-00) is a special value in SAP HANA. Configure the service details, test the connection, and create the new linked service. Step 1. In HANA SQL, this can be achieved using built-in functions such as UTCTOLOCAL, LOCALTOUTC, TO_TIMESTAMP. In both cases the system allows you. The parameter <date> must be implicitly or explicitly converted to one of the following SQL data types: The SQL data. 2 Answers. Posting Dates and Posting Periods. I have tried using YYYY-MM-DD and YYYYMMDD format. 2. Create table T2, and insert two rows into it. There are two options when connecting to SAP HANA: Treat SAP HANA as a multi-dimensional source (default): In this case, the behavior is similar to when Power BI connects to other multi-dimensional sources like SAP Business Warehouse, or Analysis. It then converts it again to an NVARCHAR type and returns the value 2009/12/31. . ADD_SECONDS The following example returns the value 5, the month for the date specified: SELECT MONTH ('2011-05-30') "month" FROM DUMMY; Converts a specified date string to a SECONDDATE data type. I want to convert them to date format. How can I have this just pass the current year? I've seen a few different potential solutions but most of them are in HANA Studio or involve dynamic SQL generation. From SAP HANA documentation: TIME. Implicit type conversion. SELECT TO_DATS ('2010-01-12') "abap date" FROM DUMMY; I have a time stamp field in a table that shows the number of seconds since 1. How to convert this datatime dd/mm/yyyy type to DATE type in YYYYMMDD format? I already tried direct mapping --> The date getting populated looks wrongUse. 0 SPS03 and 2905198 – What’s HANA Behavior when Calculation Days Between?. XMLEXTRACT Function (Miscellaneous) XMLEXTRACTVALUE Function (Miscellaneous) XMLTABLE Function (String) YEAR Function (Datetime) YEARS_BETWEEN Function (Datetime) Aggregate Functions. With this feature you can use a new safety-stock method: time-dependent days of supply. That means that 14:34:06 is stored. 764. SAP Help Portal The following example converts the value 10. 2. Even though an empty date looks like a NULL or unknown value, it is not. 4, the source and target databases are Oracle 10g. This document. 0 SPS 06 ; 2. 0 SPS 07. 0 SPS 05 ; 2. 0 SPS 01 ;. Solutions for each case: If DocDate type is NVARCHAR 8 with format YYYYMMDD: select * from OPDN A where A. 0 SPS 04 ; 2. In this way, low-latency connectivity between SAP HANA on Azure (Large Instances) units and virtual machines. 789 to DECIMAL with 10 for precision and 0 for scale, and returns the value 1234 (scale is defined as 0, so everything is truncated after precision). SAP HANA Cloud. Convert date to SAP HANA DATE format with TO_DATE. QRC. These integrity checks are performed on block level. Delta memory is always table specific and not common for all column store table. As a developer, one way to retrieve only the data your customers need is by applying a filter to your calculation views in SAP HANA Studio. It also satisfies a IS NOT NULL predicate. This site uses cookies and related technologies, as described in our privacy statement, for purposes that may include site operation, analytics, enhanced user experience, or advertising. 0 SPS 04. 0 SPS 04 ; 2. . 6. MONTH: Full name of month. 0 SPS 05 ; 2. [1] It integrates functions from lines of businesses as well as industry solutions, and also re-integrates. SAP HANA Modeling Guide for XS Advanced Model . 1. SAP HANA Smart Data Integration (SDI for short) and Smart Data Quality (SDQ for short) are the data integration and data quality. 0), Supplier Relationship Management (SRM 7. SAP HANA supports 7 categories of SQL data types and it depends on the type of data you have to store in a column. KEY; SELECT * FROM T; KEY. 5 (SPS 05) Available Versions: 2. datatable where start_time >= '2015-09-05 00:00:00' and start_time <= '2015-09-05. Hana Studio-Calculation Views-Calculated Column Date field to Year. Create table T2, and insert two rows into it. I chose SQL. 0), Customer Relationship Management (CRM 7. Vote up 0 Vote down. CURRENT TIMESTAMP Default. ADD_MONTHS_LAST: ADD_MONTHS_LAST(date, months) Computes the specified date plus the specified number of months. datatable where start_time >= '2015-09. The first part can be done in HANA like this: to_date ( "<year_column>" || "<month_column>", 'YYYYMM') as newDate. ADD_MONTHS. The following table gives the list of data types in each category −. Please have a look at the other post to get a better understanding of the context for this example . 0 SPS 07 ; 2. We need to convert a datetime column ('01/01/2009 17:13:00') to a simple date ('01/01/2009 00:00:00'). In SAP we have standard FM which can convert this decimal timestamp into Date and Time separately. This site uses cookies and related technologies, as described in our privacy statement, for purposes that may include site operation, analytics, enhanced user experience, or advertising. The following example returns a value similar to 2010-01-01 16:34:19. 7,464,851,370,000 (This shows the number of seconds that elapsed since 1. 0 SPS 03 ; 2. However, the actual content of the data blocks is not analyzed. SAP HANA SQL FLOAT TO DATE. Computes the specified date plus the specified number of days. 0 SPS 03 ; 2. Refer to the Introduction and blog post indexes here: As you saw in the previous blog post, in the Basic CDS view the data has been read with a simple ‘Select’ statement. BUDAT,112), where "112" refers to yyyyMMdd. This site uses cookies and related technologies, as described in our privacy statement, for purposes that may include site operation, analytics, enhanced user experience, or advertising. SAP HANA ( HochleistungsANalyseAnwendung or High-performance ANalytic Application) is an in-memory, column-oriented, relational database management system developed and marketed by SAP SE. 0 (or another format like Jan 11, 2010 1:30:00. 5 AS INTEGER) "cast" FROM DUMMY;Computes the specified date plus the specified number of months. The column-oriented in-memory database design allows you to run advanced analytics alongside high-speed transactions –. hana日期函数. Example The following example returns the value 2,678,400 as the number of seconds between the two specified dates:So valid values such as: 201703290 (yep 2017 valid year 03 a valid month and 29 a valid day in that month for that year. SAP HANA SQL Reference Guide for SAP HANA Platform. KEY; SELECT * FROM T; KEY. SAP HANA SQL Reference Guide for SAP HANA Platform. 0 SPS 05 ; 2. Please suggest me, how we need to get Year value from. 1. then the conversion is performed using the date format model. and specify the tenant database to recover. SELECT TO_VARCHAR (TO_DATE ('2009-12-31'), 'YYYY/MM/DD') "to varchar" FROM DUMMY; Converts a date string from format YYYY-MM-DD to date format YYYY/MM/DD.