Select query datediff negative and positive

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select query datediff negative and positive

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2008-12-08 : 20:55:46

I have the following query when the condition becomes true then show the number of days in negative (minus sign), without converting to varchar. keeping it(days field) as int datatype

Select Orderid, 'ORD' as Type, Orderdesc,
[days] = CASE WHEN duedate < GetDate()
THEN DATEDIFF(Day, duedate, GetDate())
ELSE CONVERTDATEDIFF(Day, GetDate(), duedate)
FROM TableOrders where AccountID = @AcctID order by days

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-12-08 : 23:53:40

try this

SELECT  Orderid      ,
        'ORD' AS Type,
        Orderdesc    ,
        [days] =
        CASE
                WHEN duedate < GETDATE()
                THEN -1 * DATEDIFF(DAY, duedate, GETDATE())
                ELSE DATEDIFF(DAY, GETDATE(), duedate)
         END
FROM    TableOrders
WHERE   AccountID = @AcctID
ORDER BY days

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-09 : 02:02:32

whats the purpose of CASE? wont datediff by itself return negative value? is just this enough?

DATEDIFF(DAY, GETDATE(), duedate)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources