-- 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.