Convert datetime to string in sql

Question

Using SQL language , trying to pull the same dates as you can see from the code as below;

      select    aa.sim
          from app_date1 aa
          left join 
            app_date2 bb on  aa.first_date=bb.start_date and
                             aa.last_date=bb.end_date


 start_date is datetime :   2015-11-09 00:00:00.0000000
 end_date is string :      Friday, August 25, 2023

how to make this code possible to run

Answer 1

"Using SQL language" isn't quite enough. It is - as you said - a language, but each database has its own flavor, especially when dealing with date values. Functions differ.

For example, in Oracle, this is what you might do.

The best option is to work with dates, not strings. Therefore, convert everything else to valid date values.

Let's presume that app_date1 table's first_date and end_date columns' datatype is date (as you didn't say different, and their name suggests so).

(I'm setting date format so that you'd know what is what; e.g. if you see something like "03.06.2023", is it 3rd of June or 6th of March?) you probably don't have to do that):

SQL> alter session set nls_date_Format = 'dd.mm.yyyy';

Session altered.

As of end_date, which is stored as a string (note that this was bad decision. Always store dates and timestamps in appropriate datatype columns), you'd apply to_char function:

SQL> select to_date('Friday, August 25, 2023',
  2                 'fmDay, fmMonth dd, yyyy',
  3                 'nls_date_language = english') date_value
  4  from dual;

DATE_VALUE
----------
25.08.2023

As of start_date - which is a timestamp - cast it to date datatype:

SQL> select systimestamp,
  2    cast(systimestamp as date) date_value
  3  from dual;

SYSTIMESTAMP                                                                DATE_VALUE
--------------------------------------------------------------------------- ----------
01-JUL-23 05.17.47.590000 PM +02:00                                         01.07.2023

SQL>

It means that final query might look like this:

select aa.sim
from app_date1 aa left join app_date2 bb 
  on aa.first_date = cast(bb.startdate as date)
 and aa.last_date = to_date(bb.end_date, 
                           'fmDay, fmMonth dd, yyyy', 
                           'nls_date_language = english');

---

Once again: if you use different database (such as MySQL or PostgreSQL or MS SQL Server or ...), code will probably be different from what I posted.