Number of Days Since Date Created

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 2008 Forums

Transact-SQL (2008)

Number of Days Since Date Created

Author

Topic

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2012-07-25 : 21:19:32

Hi there, I am trying to create a script that will find the number of days since a record has been cretaed.

My simple SQL statement is

select datecreated, getdate() as today from tableA

will give me the following results

datecreated.................Today

2012-07-26 11:17:06.397.....2012-07-26 11:17:06.397
2012-06-19 11:17:06.397.....2012-07-26 11:17:06.397
2012-06-18 11:17:06.397.....2012-07-26 11:17:06.397
2012-05-20 11:17:06.397.....2012-07-26 11:17:06.397

The question I have is how can I add a third column that is the difference between today and datecreated?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-25 : 21:50:57

[code]select datecreated, getdate() as today,datediff(dd,datecreated,getdate()) as dayselapsed from tableA[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2012-07-26 : 00:26:25

Thanks for that, how can I use that as a trigger to perform an action?

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-07-26 : 01:50:40

Purpose behind using above code in trigger ???

--------------------------
http://connectsql.blogspot.com/

KingCarlos
Yak Posting Veteran

74 Posts

Posted - 2012-07-26 : 01:58:05

Hi there,

I am calcultaing the days and as it reaches a certain number of days the priority will escalate

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-07-26 : 04:53:54

Why you dont like to create job for this purpose with following simple update script. Run this job with daily schedule and it will work for you.

UPDATE tableA
SET Priority = 1
WHERE datediff(dd,datecreated,getdate()) >30

--------------------------
http://connectsql.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-26 : 10:10:26

quote:
Originally posted by KingCarlos

Hi there,

I am calcultaing the days and as it reaches a certain number of days the priority will escalate

no need of trigger for that.
See the logic lionofdesert posted. it should work for you

you can add a step in your job to include notification by means of email based on this priority also

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources