SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Number of Days Since Date Created		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

KingCarlos
Yak Posting Veteran

Australia
60 Posts
Posted - 07/25/2012 :  21:19:32  Show Profile  Reply with Quote
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

India
47173 Posts
Posted - 07/25/2012 :  21:50:57  Show Profile  Reply with Quote 
select datecreated, getdate() as today,datediff(dd,datecreated,getdate()) as dayselapsed from tableA


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

KingCarlos
Yak Posting Veteran

Australia
60 Posts
Posted - 07/26/2012 :  00:26:25  Show Profile  Reply with Quote
Thanks for that, how can I use that as a trigger to perform an action?

Go to Top of Page

lionofdezert
Aged Yak Warrior

Pakistan
864 Posts
Posted - 07/26/2012 :  01:50:40  Show Profile  Visit lionofdezert's Homepage  Send lionofdezert a Yahoo! Message  Reply with Quote
Purpose behind using above code in trigger ???

--------------------------
http://connectsql.blogspot.com/
Go to Top of Page

KingCarlos
Yak Posting Veteran

Australia
60 Posts
Posted - 07/26/2012 :  01:58:05  Show Profile  Reply with Quote
Hi there,

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

lionofdezert
Aged Yak Warrior

Pakistan
864 Posts
Posted - 07/26/2012 :  04:53:54  Show Profile  Visit lionofdezert's Homepage  Send lionofdezert a Yahoo! Message  Reply with Quote
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/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts
Posted - 07/26/2012 :  10:10:26  Show Profile  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000