set date as 23:59:59 pm instead of 12 am

I am sending a date variable for start time and end time
The start time is find being automatically set to 12 am
However I need to end date to be set to 23:59:59

below are the code fragments that address the date:


 IN fromDate varchar (50),
        IN toDate varchar (50),
        IN timeZone varchar (50))

    BEGIN
            DECLARE startDate varchar (50);
            DECLARE endDate varchar (50);
            DECLARE mylogID Int;

             SET startDate = FROM_UNIXTIME(fromDate/1000);
             SET endDate = FROM_UNIXTIME(toDate/1000);

time_to_sec(timediff(endDate, startDate)) / 3600 AS totalTimeRange

Open in new window

portlightAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Dan CraciunConnect With a Mentor IT ConsultantCommented:
Well, replace
time_to_sec(timediff(endDate, startDate)) / 3600 AS totalTimeRange

with
time_to_sec(timediff(DATE_ADD(endDate, INTERVAL 1 DAY), startDate)) / 3600 AS totalTimeRange

This will add a day to your endDate, effectively putting you within 1 sec of your target time.

HTH,
Dan
0
 
Dan CraciunIT ConsultantCommented:
I don't get it. 12 am is 00:00:00. Why do you need the end time to be a second before start time?
0
 
portlightAuthor Commented:
If the start date is 1/4/2014 the time always is 00:00:00
If the end date is 1/6/2014 the time is always 00:00:00
 
There 23 hours, 59 minutes and 59 seconds left in the 1/6/2014 day that are exlcuded from the range difference.
so I need the range to be midnight of the start date to 23:59 on the end date
NOT midnight to midnight - leaving out a full day.
0
 
QuinnDexCommented:
how are you storing end date, for the time to be 00:00:00 it would indicate you are storing date only, that works for start date but for end date you need to store date and time, you can hard code the 23:59:59 into the code.

so you would store date and time in this format "2014/01/06 23:59:59"
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.