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

263 questions
61
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
  • 7,860
  • 18
  • 77
  • 118
27
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
  • 7,430
  • 12
  • 75
  • 110
12
votes
9 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
  • 121
  • 1
  • 1
  • 3
7
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,189
  • 2
  • 15
  • 33
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,042
  • 5
  • 31
  • 58
7
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
  • 71
  • 1
  • 1
  • 2
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
  • 1,963
  • 9
  • 31
  • 66
6
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,029
  • 5
  • 16
  • 37
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
  • 371
  • 3
  • 15
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
  • 3
  • 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
  • 3,502
  • 8
  • 27
  • 39
5
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
  • 11,734
  • 21
  • 62
  • 104
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
  • 16,614
  • 19
  • 77
  • 92
4
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
  • 11,826
  • 45
  • 133
  • 227
3
votes
1 answer

Oracle to_date function with codeigniter

I am using Codeigniter to insert data into an Oracle table which contains a date field. Sample code looks like this: $upload_details = array("user_name" = $name, "age" = $age, "date" = 'to_date($date,…
WebNovice
  • 2,156
  • 3
  • 20
  • 39
1
2 3
…
17 18