Questions tagged [str-to-date]
139 questions
0
votes
1 answer
MySQL: Convert from Varchar to Date and applying Date_Sub
I am trying to use 'date-sub' to subtract few month using MySQL.
Before I do that, I figure I have to covert varchar(7) value into date format first.
So, I used following:
select str_to_date('7-2016', '%m-%Y'); -- 2016-07-00 (result)
The result…
Java
- 1,208
- 3
- 15
- 29
0
votes
1 answer
How to convert mixed dates in MYSQL to yyyy-mm-dd format
I have a text type column named start_date that contains many dates in 2 date different formats. One is dd/mm/yyyy and the other is yyyy-mm-dd.
I need to convert all dd/mm/yyyy dates to yyyy-mm-dd and then set the column data type to date.
UPDATE…
ben
- 33
- 4
0
votes
2 answers
issue with mysql STR_TO_DATE() function ending characters
I am trying to use the str_to_Date function to convert a string like
'30/09/2010 3:33:51 p.m.' and '30/09/2010 12:00:00 a.m.'(excluding quotations)
to convert into a date time.
However, I am having trouble adding the last characters being 'p.m.'…
0
votes
1 answer
str_to_date change year data MySQL
I am trying to change the format of my date which is mm/dd/yyyy to MySQL format yyyy-mm-dd.
But it changes the data year to 2020.
The product_history table:
log_date id stock
10/30/2018 1001 59
10/30/2018 1002 35
10/30/2018 …
huy
- 1,648
- 3
- 14
- 40
0
votes
1 answer
Trying to take a date as input in python and updating value in mysql table using mysql connector
My aim here is to update an existing table in mysql database taking all the variables for the update statement as input from the user. I am using mysqlconnector to interface with python.
coltbu=input("Now enter the name of the column whose value…
Alizia Kumail
- 1
- 1
0
votes
2 answers
Issue with str_to_date() function with Empty field
In python, I am trying to insert some data into my table;
try:
cursor.execute("INSERT INTO covid_testtable (pid, age, state, city, notes, backnotes, type, nationality, status, announced, changed) " +
"VALUES (%s, %s, %s,…
RootPhoenix
- 1,626
- 1
- 22
- 40
0
votes
2 answers
What is oracle format for last .0 in TO_DATE ('2020-04-01 00:00:00.0','YYYY-MM-DD HH24:MI:SS.TZR')?
I got this date from Java and is date type.
So trying to format it in oracle
select TO_DATE ('2020-04-01 00:00:00.0','YYYY-MM-DD HH24:MI:SS.TZR') from DUAL
not able to format last .0 in date. Tried various way
.TZR
TZR
Not sure how to format…
fatherazrael
- 5,511
- 16
- 71
- 155
0
votes
1 answer
NiFi toDate() Function altering the content
i have been trying to convert a string to date using
${test:toString():toDate('dd-MMM-yy HH.mm.ss.SSSSSSSSS'):format('dd-MMM-yy HH.mm.ss.SSSSSSSSS')}
my value for test attribute is like 13-MAR-20 15.50.41.396000000
when i'm using the above…
Adithya Sajjanam
- 53
- 1
- 7
0
votes
2 answers
MySQL INSERT INTO SELECT not matching records are triggered by function str_to_date
I'm having a weird behavior of MySQL INSERT SELECT where I need to convert the dt_int from TABLE2 to the date time dt in TABLE1.
The table structure is
TABLE1
PK INT(11) -- auto increment
dt datetime
TABLE2
PK INT(11) -- auto increment
dt_int…
SomeoneWhoCodes
- 5
- 2
0
votes
0 answers
How to set year to 2020 if there is no year set on a VARCHAR date
I'm working with a quite old database... And dates are stored as VARCHAR, and I need to keep dates as VARCHAR :S but expanding it with year=2020, hour=0, minute=0, and second=0 if that fields are not set.
Dates have all kind of weird formats, such…
Windgate
- 365
- 1
- 4
- 14
0
votes
1 answer
Return DATE when year, week number and weekday given in one mysqli query
I have the following table :
--------------------------------------
| Year | Week | Weekday | Date |
--------------------------------------
| 2019 | 20 | 3 | |
| 2019 | 10 | 4 | |
| 2019 | 2 | 1 …
Peter Verreyde
- 39
- 7
0
votes
2 answers
MySQL = Grouping Sales Count by Months, then Displaying months as Names (Jan, Feb...DEC) not Numerical Value (1,2,3-12)
This seems like a reasonably simple problem but I cannot figure out how to change a numerical value of a month into the months actual name after the sales count is grouped by the month.
Basically I want to list the total amount of sales for each…
RickPeck
- 165
- 2
- 14
0
votes
3 answers
SQL STR_TO_DATE
I'm having trouble with the following code:
INSERT into `fun` ( funner)
SELECT YEAR(STR_TO_DATE(SUBSTRING(time,1,4), '%Y'))
FROM `orig`
returning the warning:
Incorrect datetime value: '1880' for function str_to_date
time is a varchar column in…
unicorns ruleee
- 23
- 4
0
votes
1 answer
'MySQL STR_TO_DATE with no day in the string
I am trying to use STR_TO_DATE in my MySQL code, but my string does not include a day.
SELECT STR_TO_DATE('August 2020', '%M %Y')
This code returns null. I can get this function to work if I have a day in my date like:
SELECT STR_TO_DATE('August…
lex449
- 139
- 1
- 12
0
votes
2 answers
str_to_date() does not work on right syntax
I am trying to casting text from type to date type to sort desc.
select str_to_date('FRI 12 MAY', '%a %e %b');
It returns null even though it is correct syntax I think.
Is there any problem on my setting in mysql? or just syntax error?
sk Jin
- 33
- 5