All Questions
69 questions
0
votes
0
answers
32
views
MySQL 5.6 STR-TO-DATE() returns incorrect date (previous month) - bug?
While looking at a first question and its answer I tried to produce a solution.
Because question doesn't mention a schema for table, whether the table data is in date format or just a string; and its ...
- 14.3k
0
votes
2
answers
86
views
STR_TO_DATE() returns wrong data from right format
Using MySQL 5.7, it's pretended a conversion from strings representing four-digit year followed by two-digit month with no any other character in the middle.
For example, running the following ...
- 143
0
votes
1
answer
43
views
php select records between 2 dates with formatted date column
I need to select orders' records between 2 dates. the date format on the database is looks like :
January 1, 2020 -> using %F %j, %Y format.
my try to get the records is:
$from_date = 'January 1, ...
- 324
-1
votes
2
answers
840
views
Insert a date in MySQL only if it is valid
I have a table with date values stored as strings, like '2012-01-15'. Some of them are invalid, like '2012-04-31'. I would like to insert the valid dates into a DATE type column in another table, and ...
- 1,937
0
votes
2
answers
105
views
MySQL - VARCHAR to DATE returns null
Using Local Storage Connection within Toad Data Point. It's set up to use MYSQL.
EXTRACT_DATE is varchar(20) in the format of: 08282020
Trying to change that format so it appears as 08/28/2020 in my ...
- 111
1
vote
2
answers
61
views
Format a string to a date in MySQL query
I have a table as shown below, the date column is a string(MMMM-yyyy).
I want to select the latest row for an ID. For ID # 2, the latest date would be August-2020 with the price of 45.40
ID Date ...
- 31
0
votes
1
answer
5k
views
mySQL - How to convert a string to an int and string to date using sql query
so I have this table that stores the quantity and created_on(date) field as a string, I want to convert the quantity field to int and the created_on field to date, because I want to manipulate the ...
- 49
1
vote
1
answer
2k
views
mysql left join with group_concat - only shows a single result
So I am not sure if I am taking the right approach but here is what I am after:
I need to get all the records from table A
Then join on table B and concatenate all the values that match a specific ID ...
- 33
0
votes
1
answer
56
views
Combining 2 columns in MySQL table to create a new one with a specific string
I have the following columns in my table:
launch_month Years
Jun 2018
July 2018
Aug 2018
I want to create a new column called 'launch_id' and combine ...
- 189
2
votes
3
answers
7k
views
Is it safe to store dates as a string in mysql?
I have to allow my users to make an update with 24 hours of delay from their previous update, so because date operations in this case are going to be on the backend, but in the web server, i can store ...
- 323
-1
votes
2
answers
104
views
Mysql field delete all characters after 8th character
I have one field in a table that has a time as a value.
table name: sessions
column: time
For example
time
-----
09:00:00 (this should be the correct time)
12:00:00:00:00
09:00:00:00
11:00:00:00:...
- 17
-3
votes
2
answers
387
views
Redshift SQL, concatinate sting and year
I have 2 variables: month and year.
Month is a string and looks like: Mar, Apr, May, Jun, etc...
Year is a number: 2019, 2020
I tried to concatinate both but it showed me a syntax error.
`month`||`-`...
- 79
3
votes
2
answers
2k
views
Comparing two ISO datetime strings in MySQL
I have a table where there are only DateTime stored as varchar's in ISO Format. I wanted to know what would be the MySQL query to compare given any random DateTime in iso format with the DateTime(in ...
- 53
0
votes
1
answer
47
views
How to select upcoming dates from mysql from following date format?
How to select upcoming date?
datetime
01/24/2020 6:15 PM,
01/39/1994 12:04 AM,
01/29/1994 12:04 AM,
01/30/2020 6:15 PM,
07/19/1994 12:04 AM
0
votes
2
answers
1k
views
Convert a string to date format and retrieve the week number
I have date of the format 15-Nov-20. I want to convert it into date format of the MySQL table (yyyy-mm-dd) using query before inserting the data into the table and want to get the week and year ...
- 181
-1
votes
2
answers
56
views
MySQL date string to date format in new columns
There is a column in a table which contains date like this:
Thursday, February 28, 2019 (All day) to Friday, March 15, 2019 (All day)
OR
Thursday, February 28, 2019 (All day)
I want to split these ...
- 566
0
votes
4
answers
817
views
Add some hours/mins to current datetime and get the future datetime
I need to get the get the future timestamp value for a particular time. I will have to add a string value to the current datetime timestamp and get the future timestamp value.
I am fetching current ...
0
votes
0
answers
21
views
Using MySQL, I need to figure out how to take two data types (text and date) and output them in sentence form
Incorporating both text and date data types in a user-defined function is proving to be more difficult than I thought. Do I need 2 distinct declare statements? And in the 'RETURNS |DATATYPE| ...
2
votes
3
answers
3k
views
MONTHNAME in Portuguese MYSQL
I´ve got a huge query (In a PHP file) that gives me a monthname from some dates:
CASE A.STATUS
WHEN 'Cancel' THEN LEFT(MONTHNAME(DT_CANCEL),3)
WHEN 'About to cancel' THEN LEFT(MONTHNAME(DATA_REGISTER)...
- 149
-1
votes
1
answer
34
views
Turn php object in to date format
I'm getting the birth date from my users from a form with a select dropdown for month, day and year. The plugin I'm using for this (Pie Register) stores this value as an object (I guess) in my ...
- 17
0
votes
1
answer
115
views
DateTimePicker.value will not accept date string I'm passing to it - vb.net
On one of my forms, I'm listing a load of services and corresponding dates.
Theres basically a list of services, a checkbox next to each one and a datetimepicker next to the checkbox. If the checkbox ...
- 775
0
votes
1
answer
1k
views
Add Index to Date String field in MySql
I have a table with approximately 2mn records. It has a column with date values in String format (similar format). Now I need to filter records based on this string date column. I tried with ...
- 3
1
vote
1
answer
176
views
MySQL STR_TO_DATE seems to fail when day has a "0"
I have a series of dates in my database that are in String format.
I am trying to convert these to date using the following command:
SELECT STR_TO_DATE(Date_Scanned, '%d/%m/%Y')FROM urls WHERE ...
- 220
-1
votes
2
answers
34
views
Excel Formula to Change String to Date
So I have 2 tables. In the first table the date is formatted as a string as follows 20030219. In my second table I have the Date as '2003-02-19'. Is there a function that I can use to change the first ...
- 77
0
votes
1
answer
32
views
MySQL: Not being able to compare string values as dates correctly
I am trying to display rows that fall between two ranges. Those two ranges will be dates. One of the column stores the date as a String in the format of "8-10-2017 / 05:00 AM"
In order to compare the ...
- 137
1
vote
2
answers
41
views
In MySQL, how can I extract years from a column of inconsistently inputted dates as strings?
Migrating data over to a new database, and want to start on the right note. Previous developer let users manually enter date ranges, and stored everything in one column as a string. Formats include (...
- 581
0
votes
2
answers
2k
views
MYSQL - DATE/TIME show last 30minutes
I have a column (lastlogin) that contains the following value = '17th May 2017 09:40:43 AM' ----
php function to create stamp:
date('jS F Y h:i:s A');
How can I do a select query that shows the (...
- 57
0
votes
1
answer
45
views
MySQL Leading 0's Date Formating
I currently have the following select statement:
SELECT CONCAT(TRUNCATE(HOUR("215:00:36")/24,0),":",HOUR("215:00:36")%24,":",MINUTE("215:00:36")) AS case_aging
This will convert h:m:s to d:h:m. This ...
- 47
0
votes
1
answer
1k
views
Sequel Pro: How to change a string to a date during import?
I'm trying to import a CSV file into a MySQL table using Sequel Pro.
I know I need to use STR_TO_DATE, but I can't figure out the right syntax. I've tried this syntax STR_TO_DATE(@$1, '%d/%m/%y') ...
- 843
0
votes
4
answers
2k
views
Fastest way to get month, day, year, from date string
I am getting a date from the mysql database:
here is what it comes out as:
2017-01-20
what would be the fastest way to get the month, day, and year, so for example, when i echo, it will be like ...
- 189
2
votes
1
answer
1k
views
MySQL STR_TO_DATE for strings with just month and year or just year
I have a column called ReleaseDate (which is a string). Most of them are in the form of %e %M %Y (25 May 1979), but there are a few where it is just %M %Y (May 1979) or %Y (1979). Unfortunately, it ...
- 870
0
votes
1
answer
49
views
Date string Manipulation
My current input table looks like below
Current_Day billing_day
Oct-13 14
Oct-13 15
Oct-13 16
Oct-13 1
Oct-13 8
Oct-13 12
I want the resultant table to look as below
...
- 407
0
votes
1
answer
472
views
MySQL: extracting date from string
Is it possible to get the date in mysql from this string: Thu Mar 10 005758 EST 2016 ? I'd like to get the date in mm/dd/yy format.
- 13
0
votes
2
answers
100
views
String to date PHP [duplicate]
I've searched many questions, but every solution I tried, failed.
Basically, I get a string, for example: "1994-10-29"
I have a Date field inside my SQL database. I want to convert this to a Date but ...
- 21
0
votes
2
answers
50
views
array conversion of date time into string
I have an array like this
A =['datetime.datetime("2015", " 9", " 18", " 8", " 1", " 19")']
while selecting query from database. I want to convert the array to a string like
datetime.datetime("...
0
votes
1
answer
194
views
Date/String Discrepancy in MySQL Queries
I'm noticing some strange behavior in MySQL regarding queries with dates in the WHERE clause. I'm fairly new to MySQL so I'm wondering if this is intended behavior or if there is something I can do to ...
- 21
0
votes
2
answers
252
views
Get a Date from Database and comparing it in Java
I'm having a hard time trying to get a date string from a MYSQL server (it's a date but i receive it as a String), and converting it to a Date format inside my android application. I need to convert ...
0
votes
2
answers
110
views
MySQL, how validate against string dates using PHP?
I am working with a table that has existing data in it..
The 'date' column has its data like so:
Saturday, September 26, 2015
Saturday, September 26, 2015
Saturday, September 27, 2015
Saturday, ...
- 962
0
votes
1
answer
628
views
PHP Date to String to compare dates
I only have basic PHP knowledge and I'm using PHP+Mysql and trying to check the difference in days between 2 dates; the 1st date is formatted by myself in the script as a string:
$f_entrega=$_POST['...
- 35
1
vote
2
answers
333
views
Converting String Data Value into date
Good Morning All;
I currently have a MySQL table where there are 3 date fields (Columns) that were loaded as strings in this format 20140101 YYYYmmdd. I would like to convert this to a date format ...
- 25
0
votes
1
answer
207
views
Obtain Date Values from Database then Set as String
How to obtain Date values from database then set it as a string. Can anyone tell me why the below code is not working properly? This is the portion giving trouble:
// Date d = rs.getDate("...
5
votes
3
answers
3k
views
How to change a string to a date during import using Sequel Pro?
I'm trying to import a file into a MySQL table using Sequel Pro.
I know I need to use STR_TO_DATE, but I can't figure out the right syntax.
I'm getting a bunch of these errors for each row:
[...
- 895
0
votes
2
answers
148
views
Convert verbose string to Date PHP [duplicate]
I ended up with the short end of the stick and a very verbose string as a date:
Sun Oct 5, 2014 10am to 11am PDT
I would like to convert this to a PHP Date object so I can write it to a MySQL ...
- 2,417
0
votes
1
answer
82
views
MysQL Date and Time Manipulation
I have a 215 character field, The 16th to the 21st characters are date in the format 030914.
I want to be able to convert that to Date and write it into an adjoining field in the MySQL format yyyy-...
- 1
1
vote
3
answers
309
views
How do I convert from a date to string in mySQL?
I need to treat a date field in mySQL as if it is a string. For the purposes of using the date in a LIKE statement:
select * from table where dob like some_string;
Doing this currently produces the ...
- 3,855
-1
votes
2
answers
86
views
Mysql Date Format convert
I want to sub string date from [09/Jul/2014:16:12:58 +0530] and want to feed it to Date type Field.
My complete query is like this
insert into SquidLog
(LocalIP,AccessDateTime,Url,...
0
votes
1
answer
52
views
How to use %20 in WebService call without showing it in UITextField
I am attempting to use a selected-date from date picker and implement it as the start date for part of my login credentials.. This date picker is integrated within UIAlert as the keyboard, and it is ...
- 242
1
vote
1
answer
149
views
make sql command by date object python
I want to write a 'select' command of mysql through python and I want to use date-time object of python:
cnxn = pyodbc.connect('DSN=aaa;')
cursor = cnxn.cursor()
cursor.execute("select * from tableA ...
- 55
2
votes
3
answers
11k
views
Convert String To Java.sql.Date
After looking through many of the threads in stackoverflow and cant seem to find the correct method im looking for. If anyone could shine a bit of light it would be great.
So I populated a JCombo Box ...
- 31
0
votes
1
answer
829
views
Using formatted String as Date in SQL Query
Before putting a date in a sql query, I format and convert this date to a String because the java.util.Date doesn't work.
//create the date format
String DATE_FORMAT = "yyyy-MM-dd hh:mm:ss";
...
- 4,754