MySQL - date difference in MySQL? - Asked By Cherifa Hima on 10-Aug-15 03:18 PM

I need my sql statement that gives me the dates like below: 
example: to extract data between  yesterday 07/21/2013 and last year date 07/20/2012.
Robbe Morris replied to Cherifa Hima on 22-Jul-13 01:44 PM
where datecolumn between @startDate and @enddate
Cherifa Hima replied to Robbe Morris on 22-Jul-13 01:55 PM
I don't want to enter the dates: I need to do something like this: date between now()-1 and Now()-365. But now()-365 is not correct when using the month of February because sometimes it has 28 days and sometimes 29 days.
Robbe Morris replied to Cherifa Hima on 22-Jul-13 05:27 PM
-- Here is a script to play around with. 
  
  
 -- @StartDate and @EndDate would be input parameters
 declare @StartDate datetime
 declare @EndDate datetime
 
 set @StartDate = cast('7/20/2012 00:00:00.000' as datetime)
 set @EndDate = cast('7/21/2013 23:59:59.998' as datetime)
 
 -- or
 
 Set @EndDate = dateadd(yy,-1,@StartDate)
 
 -- Local variables
 declare @StartDay int
 declare @StartMonth int
 declare @StartYear int
 declare @StartDateNew datetime
 declare @EndDay int
 declare @EndMonth int
 declare @EndYear int
 declare @EndDateNew datetime
  
 set @StartDay = datepart(day,@StartDate)
 set @StartMonth = datepart(month,@StartDate)
 set @StartYear = datepart(yy,@StartDate)
 set @EndDay = datepart(day,@EndDate)
 set @EndMonth = datepart(month,@EndDate)
 set @EndYear = datepart(yy,@EndDate)
 
 
set @StartDateNew = cast(cast(@StartMonth as varchar(2)) + '/' +
      cast(@StartDay as varchar(2)) + '/' +
      cast(@StartYear as varchar(4))+ ' 00:00:00.000' as datetime)
 
-- .999 triggers interesting results and will sometimes get data for the next day.
-- So, I play it safe with .998 for the millisecond.
 
set @EndDateNew = cast(cast(@EndMonth as varchar(2)) + '/' +
      cast(@EndDay as varchar(2)) + '/' +
      cast(@EndYear as varchar(4))+ ' 23:59:59.998' as datetime)
 
print @StartDateNew
print @EndDateNew


Update, ugh. Just realized this was MySQL. I'm not sure these date functions are exactly like SQL Server. Sorry.