to_date is sql function that converts string to date
to_date is an SQL function that converts a string to a date.
Reference
Questions tagged [to-date]
293 questions
1
vote
1 answer
Snowflake SQL - convert mmddyyyy to mmyyyy and then group by mmyyyy
Working in snowflake sql. I have a date field that is mm-dd-yyyy. I want to convert it to mm-yyyy and then group the rest of the query at the mm-yyyy level.
I have:
select
SFDC_ACCOUNT_KEY,
date_trunc('month',DATE_CURRENT) as mm_yyyy,
…
aks85
- 695
- 3
- 11
- 24
1
vote
4 answers
Can TO_DATE function return a TIMESTAMP?
Can, in any form, TO_DATE function return a TIMESTAMP? Example:
I have the following input: '2019-05-31 13:15:25.000000000'
I want to have '2019-05-31 13:15:25' as a result using TO_DATE and not TO_TIMESTAMP.
I tried:
select…
Haha
- 973
- 16
- 43
1
vote
2 answers
Spark to_date generates strange 5 digit year
I have issued the following select statement against a HIVE table in HUE editor and got below result.
SELECT statestartdate,
to_date(from_unixtime(statestartdate, 'yyyy-MM-dd HH:mm:ss')) AS statestartdatestr
FROM…
F. Aydemir
- 2,665
- 5
- 40
- 60
1
vote
2 answers
Q: How to retrieve date with time(hours,minutes,seconds) from a 19 digit number in Oracle
I want to get date and time from a 19 digit number in Oracle and could not find any solution for this big numbers. The time should be in the format HH24:MI:SS. I am able to retrieve the date but unable to get the time.
**Example Numbers to get the…
Dhanesh Agrawal
- 323
- 2
- 6
- 16
1
vote
0 answers
Oracle to_date return incorrect result
I have a table 4 columns - Code, Status, EffectiveDate (EFF_DT), EndDate (END_DT). All the columns are Varchar2 type. EFF_DT and END_DT has ISO format date (YYYY-MM-DD) with NULL values for few rows. Need to get the rows which has END_DT greater…
Partha Dasgupta
- 11
- 1
1
vote
1 answer
Oracle: TO_DATE can not parse result of TO_CHAR
I try to parse a time-string on a Oracle 12. Problem is the TZH I think.
SQL> select to_char(current_timestamp, 'DD.MM.YYYY HH24:MI TZH') from dual;
TO_CHAR(CURRENT_TIME
--------------------
05.07.2018 16:55 +02
But it is not working the other…
chris01
- 10,921
- 9
- 54
- 93
1
vote
2 answers
How to sum column which stores times in hh24:mi:ss in Oracle Sql?
I have a PROCESS_TIME with data type VARCHAR2(32),
in which I store the times as hh24:mi:ss,
and I intend to add these times on some grouping logic.
A minified version of the table
CREATE TABLE "SCHEMA"."MY_TABLE"
( "MY_TABLE_ID" VARCHAR2(32…
Chaipau
- 199
- 1
- 5
- 14
1
vote
3 answers
SQL TO_DATE - for every single date field?
After years of using MySQL, having to move a table over to Oracle SQL (am using SQL Developer). Created the table and now just wanted to check it with a single INSERT statement, getting this:
INSERT INTO table_name
VALUES…
BigRedEO
- 807
- 4
- 13
- 33
1
vote
2 answers
Trouble with Oracle date format
Query 1: Redundant to_date usage:
SELECT 1
FROM dual
WHERE '22-APR-2018'>add_months(to_date(
(to_date('28-02-2018' ,'dd-mm-yyyy') ) ,'dd-mm-yyyy'),60);
--
Query 2: Single to_date usage:
SELECT 1
FROM dual where…
Vivek
- 4,452
- 10
- 27
- 45
1
vote
2 answers
oracle 12c select to_date(' ',' ') dt from dual; returns 2017-10-01
somehow select to_date('space','space') dt from dual;
returns some date 2017-10-01,
but to_date('','space') or to_date('space','') returns null as expected.
where "space" is chr(32)
Any idea?
Thanks
Andrei Z
- 21
- 3
1
vote
1 answer
oracle to date for MON-DD gives ORA-01839: date not valid for month specified
We have a below query like
SELECT TO_DATE(CREATED_DATE_IN_CHAR,'MON-DD') FROM TABLE1
Data in the columns are in the same format of MON-DD.
on executing the above query we are getting ORA-01839: date not valid for month specified.
On further…
Hariharan
- 21
- 2
1
vote
1 answer
ORACLE 10g : To_date() Not a valid month
So I have this little script :
set serveroutput on;
DECLARE
val DATE;
BEGIN
val := TO_DATE('27-Jan-2001','DD-Mon-YYYY');
dbms_output.put_line(val);
END;
The mask match with the date I'm inputing but it displays
ORA-01843: not a valid…
Scarrs
- 13
- 1
- 3
1
vote
4 answers
Oracle : Error while Extracting Year and Month from TimeStamp
I have time stamp values such as 30/05/06 11:40:34.000000000 AM in the column. I need to extract Year (i.e. 2006) and the month (i.e. 05) from this value. Below is my SQL.
SELECT EXTRACT(YEAR FROM TO_DATE('19/10/09 10:45:44.000000000 AM')) FROM…
Kiran Bandal
- 15
- 3
1
vote
2 answers
Using TO_DATE() with AM/PM Formatting
I am trying to select some dates from a table where the format of the dates is like this:
14-APR-14 10.35.00.0000000000 AM
01-NOV-16 02.43.00.0000000000 PM
Note that the dates can be either AM or PM, but when I try to do a simple SELECT from the…
BlargZombie
- 11
- 1
- 3
1
vote
1 answer
Java-To_Date() inside Callable Statement
I need to execute the below procedure with 2 input param in Java. It should get executed successfully, there is no output param required.
Can anyone please help me with the code.
SQL Statement:
call…
Ketaki
- 31
- 4