SQL query with Truncate and %

[fpineda101]

I'm trying to get the time remaining from a 7 day auction. There is a timestamp (datecreated) which has 7 days added to it. I want to find out how much time remains in days, hours, minutes and seconds when a page is refreshed...

This is what I have so far. It tells me there is a error "Incorrect syntax near keyword 'truncate'"

SQL = "Select truncate((datecreated + 7) - GetDate()) + ' days ' " _
& "+ truncate( ((datecreated + 7) - GetDate() )%24, 24) ) + ' hours ' " _
& "+ truncate( ((datecreated + 7) - GetDate() )%24 * 60, 60) ) + ' minutes ' " _
& "+ truncate( ((datecreated + 7) - GetDate() )%24 * 60 * 60, 60) ) + ' seconds remain' " _
& "FROM table_name"

Any help would be appreciated!

 

[TechGnome]

Since you are using GetDate() can I assume SQL Server? If that's the case, check SQL Booksonline for "DateDiff"

-tg

 

[fpineda101]

SELECT string has been changed...

SQL = "Select abs( datediff( day, DateCreated + 7, getDate() ) ) + " _
& "abs( datediff( hour, DateCreated + 7, getDate() ) ) + " _
& "abs( datediff( minute, DateCreated + 7, getDate() ) ) + " _
& "abs( datediff( second, DateCreated + 7, getDate() ) ) " _
& "FROM table_name"

I want to be able to insert words like days, hours, minutes and seconds after each DateDiff function - sort of like the Oracle || ' days ' || ' hours ' || code - so when I perform an ExecuteScalar, this all comes out as one value returned

I obviously need to do some calculation to get the hours after the days have been calculated and the same for minutes after hours have been calculated etc...

In this scenario can someone tell me where I would perform the modulus calculation?

Much thanks for your help

 

[fpineda101]

My new query

SQL = "Select floor( datediff( day, DateCreated + 7, getDate() ) ) + " _
& "floor( (datediff( hour, DateCreated + 7, getDate() )*24) % 24 ) + " _
& "floor( (datediff( minute, DateCreated + 7, getDate() )*24*60) % 60 ) + " _
& "floor( (datediff( second, DateCreated + 7, getDate() )*24*60*60) % 60 ) " _
& "FROM table_name"

An error thrown - Arithmetic overflow error converting expression to data type int

I've used Floor() and ABS(). I don't know what would be the correct function to use here. I know I'm almost there. Please help!

 

[fpineda101]

Resolved

Here is the query to perform this on SQL SERVER

select convert(varchar(10), sec / (24 * 60 * 60)) + ' days ' +
convert(varchar(2), sec / (60 * 60) % 24) + ' hours ' +
convert(varchar(2), (sec / 60) % 60) + ' mins ' +
convert(varchar(2), sec % 60) + ' secs remaining'
from
(
select datediff(second, getdate(), dateadd(day, 7, datecreated)) as sec
from
(
select datecreated FROM table_name WHERE <your where clause>
) c
) s