how to get yesterday date if date is stored in varchar type format is (mm-dd-yyyy) for Event scheduler

impala date format yyyymmdd
impala convert datetime to date
impala week number
impala cast
impala timestamp where clause
impala first day of month
impala convert unix timestamp to date
impala difference between two timestamps

I have table called time where date column store date in varchar type format is (m-d-y)

I'm running an event at 1am every night inserting the data into the daily data table form time table. in below formula i have given the A.Date value of yesterday (example if today (01-12-2019) at 1AM event run it should pick the date of yesterday(01-11-2019)

INSERT INTO dailydata 
SELECT A.Date, A.EmpID, B.name, B.TeamName, 
SUM(CASE WHEN State = 'active' THEN A.MinutesatState ELSE 0 END) AS active,
SUM(CASE WHEN State = 'idle' THEN A.MinutesatState ELSE 0 END) AS idle,
Min(A.Statestarttime) AS Stime, MAX(A.StateEndtime) AS Etime,
ROUND((TIME_TO_SEC(MAX(A.StateEndtime))/60 - 
TIME_TO_SEC(MIN(A.StateStarttime))/60)/60,2) as Inofficehr, 
ROUND(SUM(CASE WHEN State = 'active' THEN A.MinutesatState ELSE 0 END)/60,2) 
AS activehr, 
ROUND(SUM(CASE WHEN State = 'idle' THEN A.MinutesatState ELSE 0 END)/60,2) 
AS idlehr 
FROM time A join ttld.login B on A.EmpID=B.username 
WHERE A.Date= subdate(current_date, 1) AND A.Statestarttime <>'' AND A.StateEndtime <>'' 
GROUP BY A.EmpID;

While running this code i'm getting a

Warning: #1292 Incorrect datetime value:

code is working fine if i change A.Date= subdate(current_date, 1) to A.Date='01-13-2019', but to make it a event i need some way to get the yesterday date automatically.

I have tried

SELECT SUBDATE(NOW(), 1);
SELECT SUBDATE(NOW(), INTERVAL 1 DAY);
SELECT NOW() - INTERVAL 1 DAY;
SELECT DATE_SUB(NOW(), INTERVAL 1 DAY);

You would need to convert the string date to a real DATE datatype before comparing it with another date. You can use STR_TO_DATE for that purpose.

Try to change :

A.Date = subdate(current_date, 1) 

To :

STR_TO_DATE(A.Date, '%m-%d-%Y') = SUBDATE(CURRENT_DATE, 1) 

Learning SQL, Each database server allows a different range of dates for temporal col- umns. Although this might not make any difference for most systems that track current and future events, it is important to keep in mind if you are storing historical dates. Date format components Here's how the various temporal types would be used​  0 how to get yesterday date if date is stored in varchar type format is (mm-dd-yyyy) for Event scheduler Jan 12 '19 0 Excel vba extract top 5 google search results URL in cell Jun 22 '18 0 Jquery or JavaScripts to add calculation column in html table [duplicate] Feb 6 '19


you can use the this (first convert varchar to date, then substract 1 day from it)

SELECT DATE_SUB(STR_TO_DATE('01/01/2019', '%m/%d/%y%y%y%y'), INTERVAL 1 DAY)

for using in where clause (index friendly approach)

Date =  DATE_FORMAT(DATE_SUB(NOW(), interval 1 DAY), '%m-%d-%Y')

Impala Date and Time Functions | 5.8.x, The underlying Impala data type for date and time data is TIMESTAMP When this setting is enabled, these functions and operations convert to and from does not match the expected YYYY-MM-DD format -- produces an invalid TIMESTAMP, with these functions, change the types of columns that store the return values,  How to convert Date to String in Java 8? How to represent fixed date like credit card expiry, YearMonth in java 8? How to see if a date is before or after another date in Java 8? How to find Date after 1 week in java 8? How to get a particular date in Java 8? How to get today's date in Java8? How can we convert character array to a Reader in Java?


I strongly encourage you to fix your date column. Do not store dates as strings. Store them as dates.

But if you are, then I would recommend doing the comparison as:

A.Date = DATE_FORMAT(SUBDATE(CURRENT_DATE, 1) , '%m-%d-%Y')

By removing the functions on A.DATE, the query can make use of indexes.

update time
    set date = str_to_date(date, '%m-%d-%Y');  -- this will set the string to the default format for a date

alter table time modify date date;

Also note that date and time are poor choices for column and table names because they are already used for both types and functions.

Impala Date and Time Functions | 6.3.x, The underlying Impala data type for date and time data is TIMESTAMP , which When this setting is enabled, these functions and operations convert to and from not match the expected YYYY-MM-DD format -- produces an invalid TIMESTAMP, Strings being converted through CAST() must still have the elements in the  select * from tablname where date between '13/11/2011' and '05/12/2011' it wont return any value because date is stored in varchar and format is dd/mm/yyyy and it is taking and comparing with first two values soo how can i get the values in between two dates Please can any one help me thanks in advance


Datetime Data Types and Time Zone Support, Oracle Database datetime and interval data types and time to store consistent information about the time of events and transactions. DATE 'YYYY-MM-DD' TO_DATE('1998-DEC-25 17:30','YYYY-MON-DD HH24:MI' If your queries use a date format without a time portion, then you  I have table called time where date column store date in varchar type format is (m-d-y) I'm running an event at 1am every night inserting the data into the daily data table form time table. in below formula i have given the A.Date value of yesterday (example if today (01-12-2019) at 1AM event run it should pick the date of yesterday(01-11-2019)


Ask TOM "DATE queries using BETWEEN", Developers and DBAs get help from Oracle experts on: DATE queries using BETWEEN. or if you just wanted to supply the days as bind variables (in the form of a string where idx_date_col between to_date('01.04.2002' , 'DD.MM.​YYYY') and I combined the two fields into one field of type date and I am storing the first  An expression of type DATE, TIME, or TIMESTAMP. binary_expr. An expression of type BINARY or VARBINARY. Optional: format. The format of the output string: For numeric_expr, specifies the SQL format model used to interpret the numeric expression. For more information, see SQL Format Models. For date_or_time_expr, specifies the expected format to


SQL - DATE and TIME Functions, DATE and TIME values in PostgreSQL have a whole special set of Dates in a database aren't stored as strings, but we input and fetch it as if it were a string with the following format for the information: TO_CHAR([date type], [pattern]) TO_CHAR(TIMESTAMP '2020-06-23 19:24:21', 'YYYY-MM-DD  How to change date format from mm/dd/yyyy to dd/mm/yyyy in sql server 2005 express edition Nov 20, 2008 08:37 AM | smartymca | LINK I have a page where i m showing some data and date which is in mm/dd/yy format in detail view,but i want to show date in dd/mm/yy format.