Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help with date function

Status
Not open for further replies.

tttggg

Technical User
Joined
May 24, 2007
Messages
61
Location
CA
I have a start date and an end date . I need to find out the date difference ( the duration )
I’m used the datediff function and got the difference in sec.
Can anyone tell how can I convert total number of sec onto “mm/dd/yyyy” format.
I used Date (sec) but it gives me wrong date.
I’m using CR 10 and SQL server 2005.

Thanks in advance
 
If you want to find de duration in number of days use:
DateDiff ("d", startDateTime, endDateTime)

DateDiff returns a numeric value, not a date value.
 
Is it possible to convert the return value to Datetime?
 
For example if the result is 12 days, how would you like the result to be formatted?
 
I want to be in the following format
yyyy/mm/dd h:m:s

Say the result is 1562 Sec then It should be
00/00/00 0:26:02

Thanks
 
I don't know how is this possible, because days, minutes and hours have always the exact number of seconds, but what about a month or a year?

Some months have 30 days, some others 31, some years have 365 hays, some 364.

As long as we need a duration of time it should be represented as a number of days, seconds, whatever.

I see no meaning in representing it as a date.

Please explain why do you need it like this?
 
Your point makes sense.
Days/Hr/Min/Sec make more sense.
If you can help me with a formula it will be much Appreciated

Thanks
 
There is an faq on how to convert seconds to the format you want: faq767-3543.

-LB
 
The logic should be like this:
1 min = 60 sec;
1 hr = 3600 sec;
1 day = 86400 sec;

NumberOfDays = NumberOfSeconds \ 86400
NumberOfSeconds = NumberOfSeconds Mod 86400

NumberOfHours = NumberOfSeconds \ 3600
NumberOfSeconds = NumberOfSeconds Mod 3600

NumberOfMinutes = NumberOfSeconds \ 60
NumberOfSeconds = NumberOfSeconds Mod 60

"\" performs integer division, and mod returns the remainder of the division.

The result will be:
NumberOfDays : NumberOfHours : NumberOfMinutes : NumberOfSeconds
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top