Questions tagged [str-to-date]
139 questions
0
votes
2 answers
STR_TO_DATE and LOAD DATA INFILE formatting issue
Well, thanks everybody for their help. Looks like it was an un-escaped character in the date formatting: '%%d/%%m/%%Y %%H:%%i' is what I needed. Since this is being run from a batch file, I didn't take that into account.
I've got a batch file that…
JubJub24
- 21
- 3
0
votes
1 answer
Ignore characters in date string when using str_to_date
I'm importing data from an XML file where the date field is stored as text. Normally this would be easy to convert within mysql, however the problem is that the dates contain the %e element stored as 1st, 2nd, 3rd, 4th, etc.. As such it makes it…
Matt Weller
- 2,684
- 2
- 21
- 30
0
votes
2 answers
Select between two dates dateformat
Maybe anyone can help me with this query. I just want to select all rows between two dates, but I'm having problems with the date-format.
My query:
SELECT id, number, date
FROM `table`
WHERE (STR_TO_DATE(date, '%j-%n-%Y') between '6-4-2015' AND…
josser1995
- 45
- 1
- 7
0
votes
1 answer
Strange behavior with str_to_date, possible mixing of tables?
I have an issue with this query and cannot figure out where the problem is coming from.
I am trying to run: update earnings_history setDate_formatted= STR_TO_DATE(temp_date, '%m/%d/%Y'); to convert my strings to dates, but I get this error.
Error…
nicholas.reichel
- 2,260
- 7
- 20
- 28
0
votes
1 answer
STR_TO_DATE() vs CONSTANT when comparing DATETIME field
There is a table with DATETIME field named 'created_at'.
I try execute two queries like this:
SELECT * FROM myTable WHERE created_at BETWEEN '2015-03-15 10:25:00' AND '2015-03-25 10:30:00';
SELECT * FROM myTable WHERE created_at…
abdulmanov.ilmir
- 1,325
- 4
- 15
- 24
0
votes
2 answers
Mysql Select statement to and convert timestamp on the fly
I have a MYSQL table which has a column name called timestamp stored in string format. the timestamps in this column are in the following format e.g. '20/10/2014 05:39 PM'
Now how can select a row and convert the timestamp column to 24HR format on…
Nithy Kumar
- 13
- 1
- 3
0
votes
1 answer
MySQL: STR_TO_DATE with YYWW
How do you use MySQL with YYWW instead of YYYYWW?
My field looks like this:
wYYWW, for example w1310, w1506 and so on.
%x and %X seems to need 4 digits, and therefor it doesn't work.
Is there anyway to solve this problem?
user2687506
- 789
- 6
- 21
0
votes
1 answer
MySQL issue updating DATETIME Field from ISO 8601 format
I have a table with a varchar field that contains ISO 8601 time format like 2015-01-18t10:00:10z.
I can use
select STR_TO_DATE('2015-01-18t10:00:10z','%Y-%m-%dt%H:%i:%s')
and get the correct result:
2015-01-18 10:00:10
When I come to use this to…
Stuart
- 35
- 5
0
votes
2 answers
Mysql str_to_date shows null instead of formatted date
I am facing a silly problem while converting datetime column into y-m-d format.
I am running following query :
SELECT STR_TO_DATE(dateadded, '%y-%m-%d') FROM my_table
Note : The dateadded column is a type of datetime.
Whenever I am running above…
Ankit
- 627
- 1
- 9
- 22
0
votes
3 answers
MySQL str_to_date conversion for selected format
Date conversion issue via STR_TO_DATE function in MySQL
SELECT STR_TO_DATE('17-JUL-14','%Y-%m-%d %h:%i:%s')
It shows as
NULL
How can I show it as:
2014-07-17 00:00:00
Thanks
Aditya P Bhatt
- 21,431
- 18
- 85
- 104
0
votes
1 answer
Convert date stored as VARCHAR in MYSQL to MYSQL Compatible Date
I have a database that I receive every month that I simply import through CSV into MYSQL. I don't worry about preserving any old database entries, so I just overwrite the old database with the new entries monthly.
I want to be able to sort through…
Stannet
- 11
- 2
0
votes
3 answers
str_to_date error in MySQL (Java)
I have the following column value which I am converting and storing in another column
Nov 22 2014 00:00:00 AM
Now I am using the following query to convert it to Date format and store in another column
UPDATE DataNov2014 SET Datee =…
Gagan93
- 1,826
- 2
- 25
- 38
0
votes
2 answers
MySQL STR_TO_DATE ends as a "0" (zero)
SELECT STR_TO_DATE('1.1.2000 0:00:00','%e.%c.%Y') will end up like 2000-01-01
but when I'm trying to do the same on column with values 1.1.2000 0:00:00 by running
SELECT
FirstDisplayedDate,
FirstDisplayedDate =…
0
votes
1 answer
Invalid result when 2 dates (String) are compared
I have a table called test in my MySQL database. Below is the how it is created.
CREATE TABLE `test` (
`update_date` varchar(10) NOT NULL,
`value` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
Below is its data.
I run the below query as…
PeakGen
- 21,894
- 86
- 261
- 463
0
votes
0 answers
String date from a csv file into mysql
I have a csv file I'm trying to parse a bunch of strings and a few are date formats in my database. The code looks a little like this:
load data infile '/temp/db.csv'
into table db.dbtable
fields terminated by '|'
lines terminated by '\n'
ignore 1…
JFrench
- 3
- 2