Asked  6 Months ago    Answers:  5   Viewed   49 times

I have a string column which acts as a date and I want to select it as a date.

Is it possible?

My sample data format would be; month/day/year -> 12/31/2011

 Answers

16

As was told at MySQL Using a string column with date text as a date field, you can do

SELECT  STR_TO_DATE(yourdatefield, '%m/%d/%Y')
FROM    yourtable

You can also handle these date strings in WHERE clauses. For example

SELECT whatever
  FROM yourtable
 WHERE STR_TO_DATE(yourdatefield, '%m/%d/%Y') > CURDATE() - INTERVAL 7 DAY

You can handle all kinds of date/time layouts this way. Please refer to the format specifiers for the DATE_FORMAT() function to see what you can put into the second parameter of STR_TO_DATE().

Tuesday, June 1, 2021
 
mistero
answered 6 Months ago
84

If you're sure you don't want to require all the date fields, just fill them with zero if they don't exist, like this:

$month = ($_POST['month'] == '') ? '00' : $_POST['month'];

otherwise, if you're worried not about blank dates, but about non-standard input, you could do:

$month = (is_numeric($_POST['month']) && $_POST['month'] > 0 && $_POST['month'] < 13) ? $_POST['month'] : '00'

$date = date('Y-m-d G:i:s', strtotime($_POST['dataThatUserEntered']));

should do the trick, no?

Just make sure you validate the user input, otherwise you may find that $date = '1970-01-01 00:00:00 if they enter something crazy.

Saturday, May 29, 2021
 
Shreejibawa
answered 7 Months ago
68
SELECT * FROM tbl WHERE datetime < NOW() - INTERVAL 1 WEEK

If your table stores datetimes in different timezone than what NOW() returns, you can use UTC_TIMESTAMP() instead to get the timestamp in UTC.

Sunday, August 8, 2021
 
c_k
answered 4 Months ago
c_k
23

You need STR_TO_DATE() to convert the string to a date. Consider:

select str_to_date(
      'Wed, 18   Oct   2017 10:11:03 GMT',
      '%a, %d %b %Y %T GMT'
 )

Yields:

2017-10-18 10:11:03

Once you strings are converted to dates, you can use timestampdiff() to compute the difference between them, and turn the result back to a time using sec_to_time():

select
    person,
    taskID,
    sec_to_time(
            timestampdiff(
            second, 
            str_to_date(Start_time, '%a, %d %b %Y %T GMT'),
            str_to_date(End_time, '%a, %d %b %Y %T GMT')
        )
    ) time_on_task
from mytable

Demo on DB Fiddlde:

| person | taskID | time_on_task |
| ------ | ------ | ------------ |
| Alpha  | 1      | 00:00:33     |
| Alpha  | 2      | 00:00:12     |
| Beta   | 1      | 00:00:46     |
| Alpha  | 3      | 00:01:10     |
| Gamma  | 1      | 03:03:42     |
| Beta   | 2      | 00:00:23     |
Friday, August 27, 2021
 
Steve Prentice
answered 3 Months ago
75

Lubridate can handle it:

> Date_String <- "19th January 2020"
> lubridate::dmy(Date_String)
[1] "2020-01-19"
Friday, September 3, 2021
 
Magnanimity
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :  
Share