SQL Format of Date/Time

Thread: SQL Format of Date/Time

1. Senior Member
Join Date
Dec 1969
Posts
236

SQL Format of Date/Time

How could we use DateAdd or DateDiff and return hours, minutes and seconds? We have code that is calculating time/date based on two datetime fields, but we can only compare one part of the date or time (for instance.. hh or yy) .

2. meg
Member
Join Date
Dec 1969
Posts
64

RE: SQL Format of Date/Time

Try:<BR><BR>SELECT DateDiff("h",[TABLE1]![DATE1],[TABLE1]![DATE2]) AS Expr1, DateDiff("m",[TABLE1]![DATE1],[TABLE1]![DATE2]) AS Expr2<BR>FROM TABLE1;<BR><BR><BR>where Expr1 gives you their hour differences and Expr2 gives you difference in months.<BR><BR>M:)

3. Senior Member
Join Date
Dec 1969
Posts
96,118

I think you misunderstand...

...how the functions work.<BR><BR>When you ask for DateDiff in seconds, it gives you the number of seconds between the two FULL DATE AND TIME values. That is, for every day between the two, you will get an additional 60*60*24 seconds, etc.<BR><BR>It doesn&#039;t give you *just* the difference between the seconds part of the date/time. It&#039;s the full difference.<BR><BR>So if you get the diff in seconds, that *should* be all you need. <BR><BR>The diff in minutes is just the diff in seconds divided by 60.<BR>The diff in hours is diff in seconds divided by 60*60.<BR>The diff in days is diff in seconds divided by 24*60*60.<BR>etc.<BR><BR>DateAdd works the same way. If you do DateAdd(s,24*60*60,someField) that is *exactly* the same as doing DateAdd(d,1,someField).<BR><BR>Or did I misunderstand you, instead?<BR><BR>

4. Member
Join Date
Dec 1969
Posts
44

Still Not Working?...

Have you tried using a combination of extracting the part of the date you want, that is time, and DateDiff() in your select statement as proposed yesterday?<BR><BR>e.g.<BR><BR>Depending upon your SQL Server&#039;s date format, you may be able to do this :<BR>____________________________<BR>Select <BR>Right(DateTimeUp,7) as &#039;TimeUp&#039;,<BR>Right(DateTimeDown,7) as &#039;TimeDown&#039;,<BR>DateDiff(hh, DateTimeUp, DateTimeDown) AS &#039;TimeDifference&#039;,<BR>DateDiff(hh, DateTimeUp, GetDate()) AS &#039;UpTimeSoFar&#039;<BR>from [tableName]<BR>____________________________<BR><BR>The Right() function, just like VBScript, should convert the date into a string and lop off the time part of your date fields depending upon how your datetime format displays. You may want to increase/decrease the number accordingly.<BR><BR>To test it just select the time part of today&#039;s date :<BR>______________________________<BR>Select Right(GetDate(), 7)<BR>______________________________<BR><BR>See if you need to adjust this and if this even works in your system.<BR><BR>HtH,<BR><BR>Reginald

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•