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 2012 Forums
 Transact-SQL (2012)
 How to convert days into number.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

NguyenL71
Posting Yak Master

USA
205 Posts

Posted - 12/03/2013 :  17:17:00  Show Profile  Reply with Quote
If the user want to archive data older than 30 than @numberofDay should convert into date like 11/03/2013.
They want to pass in days as int and the process should convert that into day.

Any help would greatly appreciate.

Declare @NumberofDay INT = 30

-- No

@NumberofDay should be something like '11/03/2013'


DELETE FROM MyBilling
OUTPUT deleted.[__$start_lsn], deleted.[__$seqval], deleted.[__$operation], deleted.[__$update_mask], deleted.[BillingDetail_ID]
INTO ArchiveAudit.MyBilling_CT ( [__$start_lsn], [__$seqval], [__$operation], [__$update_mask], [BillingDetail_ID]

WHERE (CONVERT(CHAR(8), [Effective_date], 112) < @NumberofDay ) -- This some how become '11/03/2013'

TG
Flowing Fount of Yak Knowledge

USA
5914 Posts

Posted - 12/03/2013 :  17:28:03  Show Profile  Reply with Quote
Do you want a date that is @numberofDays less than today?


declare @numberOfDay int; set @numberOfDay = 30
select dateadd(day, datediff(day, 0, getdate()), -@numberOfDay)

OUTPUT:
2013-11-03 00:00:00.000


So you probably want:

WHERE Effective_date < dateadd(day, datediff(day, 0, getdate()), -@numberOfDay)


Be One with the Optimizer
TG

Edited by - TG on 12/03/2013 17:29:32
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/03/2013 :  23:43:38  Show Profile  Reply with Quote
quote:
Originally posted by NguyenL71

If the user want to archive data older than 30 than @numberofDay should convert into date like 11/03/2013.
They want to pass in days as int and the process should convert that into day.

Any help would greatly appreciate.

Declare @NumberofDay INT = 30

-- No

@NumberofDay should be something like '11/03/2013'


DELETE FROM MyBilling
OUTPUT deleted.[__$start_lsn], deleted.[__$seqval], deleted.[__$operation], deleted.[__$update_mask], deleted.[BillingDetail_ID]
INTO ArchiveAudit.MyBilling_CT ( [__$start_lsn], [__$seqval], [__$operation], [__$update_mask], [BillingDetail_ID]

WHERE (CONVERT(CHAR(8), [Effective_date], 112) < @NumberofDay ) -- This some how become '11/03/2013'


see
http://visakhm.blogspot.in/2012/07/generate-datetime-values-from-integers.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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