Questions tagged [str-to-date]
123 questions
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
- 1,998
- 5
- 15
- 27
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,086
- 4
- 13
- 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
- 2
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
- 673
- 4
- 18
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
- 487
- 1
- 7
- 21
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
- 19,393
- 17
- 78
- 102
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
Conditional str_to_date() if format is of one type, else do nothing
I have a table with dates, some of which have this format 31-DEC-2010 while others have this format 2011-01-13.
I am trying to get all them having the date format, using the str_to_date() function, but it fails since it can not convert 2011-01-13…
adrCoder
- 2,821
- 1
- 23
- 40
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,657
- 2
- 19
- 36
0
votes
1 answer
MySQL: str_to_date multiple formats
I have a ETL cleanup project where I have a unioned date column that includes formats such as "2014-10-14" and "10/14/2014 12:00:00 AM". I am trying to find a slick way I can convert them all using a str_to_date function either in a case statement…
Eric Petersen
- 53
- 1
- 5
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
- 18,056
- 71
- 208
- 385
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