Stack Overflow
Stack Overflow

Questions tagged [to-date]

to_date is sql function that converts string to date

to_date is an SQL function that converts a string to a date.

Reference

293 questions
81
votes
5 answers

Using Oracle to_date function for date string with milliseconds

I have to perform some inserts into an Oracle DB. I have some dates in the following format '23.12.2011 13:01:001' Following the documentation I wrote inserts to_date as follows: to_date('23.12.2011 13:01:01', 'DD.MM.YYYY HH24:MI:SS') which works…
Luixv
  • 8,590
  • 21
  • 84
  • 121
31
votes
2 answers

String to date in Oracle with milliseconds

I want to convert the follow string to date: 2004-09-30 23:53:48,140000000 I tried: to_date('#', 'YYYY-MM-DD HH24:MI:SS,FF9') But PL/SQL keep throwing this error: ORA-01821: date format not recognized. FF9 is incorrect for Oracle, any suggestion?…
Custodio
  • 8,594
  • 15
  • 80
  • 115
16
votes
10 answers

Convert text to timestamp in redshift

I have a text field "presence_changed_at" with text values i.e. '2014/12/17 08:05:28 +0000. I need to convert this into timestamp. In postgreSQL there is function TO_TIMESTAMP(), however in redshift this does not seem to be supported. I can get the…
user5157294
  • 161
  • 1
  • 1
  • 3
12
votes
4 answers

Function "TO_DATE" not found in H2 database

I have a SQL statement and trying execute with H2 in-memory database in Java. The following exception thrown. SQL: SELECT ACCT_RULE_ID, ACCT_ACTION_ID FROM ACCT_RULE WHERE (ACCT_ACTION_ID = ?) AND (START_DATETIME <= to_char(?, 'mm/dd/yyyy…
user3268428
  • 121
  • 1
  • 1
  • 3
8
votes
5 answers

TO_DATE problems

I have the following in my SQL where clause. This is running against an Oracle database. The sc_dt field is defined in the db as a date field. sc_dt = TO_DATE('2011-11-03 00:00:00.0', 'YYYY-MM-DD') produces the following error "date format picture…
acedanger
  • 1,197
  • 2
  • 15
  • 34
7
votes
2 answers

Oracle to_date function. Mask needed

I have string of date from xml file of such kind: '2010-09-09T22:33:44.OZ' I need to extract only date and time. I want to ignore symbol T and .OZ (time zone). Which mask I should use? Thanks in advance
Andrey Khataev
  • 1,303
  • 6
  • 20
  • 46
7
votes
4 answers

Oracle: Avoiding NULL value in to_date

I have a functional select statement that has a where clause, in the where clause there is a statement like so... to_date(camp.start_date, 'MM/DD/YYYY') >= to_date(:from_date, 'YYYY-MM-DD HH24:MI:SS') However, if camp.start_date is NULL or has…
HelloWorld
  • 4,251
  • 8
  • 36
  • 60
7
votes
1 answer

Setting the Date Format in the SQL*Loader Control File

I have csv file that has "17 September, 2009 11:06:06 AM" as COMPLETED_ON variable I am using sql loader to load data to oracle with folowing: LOAD DATA INFILE 'c:/load/file_name.csv' APPEND INTO TABLE tbl_name FIELDS TERMINATED BY ',' OPTIONALLY…
Angelina
  • 2,175
  • 10
  • 42
  • 82
6
votes
4 answers

Literal does not match format string for Oracle SQL to_date on a string column

Dear SQL Gurus from Stack Overflow: Environment: Oracle I'm trying to understand why I can't do a to_date selection on a table column that contains strings. Note tableZ with a column of name Value in the example below contains a bunch of strings,…
Rio
  • 14,182
  • 21
  • 67
  • 107
6
votes
2 answers

Oracle uses or ignores indexed column depending on the format of to_date(literal)

I'm using an indexed column used as a filter by putting it 'between' two literal values. (The column is in the second position of the index and actually makes execution slower; I will deal with that later). What's confusing me is that Oracle…
arielCo
  • 413
  • 5
  • 16
5
votes
1 answer

Oracle TO_DATE function

I always thought that to_date function string and date format should match. Why is the below statement working fine? select TO_DATE('20151014','yyyy-mm-dd') from dual; Is oracle now ignoring the special characters before converting string to…
spider8
  • 157
  • 1
  • 4
  • 13
5
votes
4 answers

Convert all selected columns to_char

I am using oracle SQL queries in an external Program (Pentaho Data Integration (PDI)). I need to convert all columns to string values before I can proceed with using them. What i am looking for is something that automatically applies the select…
aldorado
  • 4,394
  • 10
  • 35
  • 46
5
votes
2 answers

Oracle SQL convert date format from DD-Mon-YY to YYYYMM

I have a to compare dates in 2 tables but the problem is that one table has the date in DD-Mon-YY format and the other in YYYYMM format. I need to make both of them YYYYMM for the comparison. I need to create something like this: SELECT * FROM…
Tom
  • 12,776
  • 48
  • 145
  • 240
4
votes
2 answers

Date and quarter SQL ORACLE

I am currently working on oracle sql and have some issues managing dates and quarters. The first thing I try to do is given a quarter and a year I would like to know the number of days of the quarter. For example, quarter 1 in 2013 had 90 days but…
Soji
  • 177
  • 1
  • 5
  • 17
4
votes
3 answers

TO_DATE returning different dates for different format masks?

I am having query on the below sql. Please help me. select to_date(sysdate, 'DD MONTH YYYY') , to_date(sysdate, 'yyyy/mm/dd hh24:mi:ss') from dual where to_date(sysdate, 'DD MONTH YYYY') < to_date(sysdate, 'yyyy/mm/dd'); 1) to_date(sysdate,…
Kanagavelu Sugumar
  • 18,766
  • 20
  • 94
  • 101
1
2 3
…
19 20