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
 General SQL Server Forums
 New to SQL Server Programming
 Getting list Of Data for Current Week

Author  Topic 

mahesh.sanka
Starting Member

18 Posts

Posted - 2013-03-12 : 05:58:52

Hi ,

In my project i have one column Policy Expiry Date which is a Date Field.

for Example

Policy Expiry Date Policy Number
------------------- ----------------
2012-03-14 00:00:00.000 A143
2089-10-31 00:00:00.000 A326
2076-11-06 00:00:00.000 A1234
2078-11-19 00:00:00.000 A4567
2012-12-01 00:00:00.000 A12345

I want to get list of the ploicies which are expiring in the current week.


Thanks & Regards,
Mahesh Kumar Sanka

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-12 : 06:38:35
DECLARE @ploicy TABLE(PolicyExpiryDate DATETIME, PolicyNumber VARCHAR(6))
INSERT INTO @ploicy
SELECT '2012-03-14 00:00:00.000', 'A143' union all
SELECT '2089-10-31 00:00:00.000', 'A326' union all
SELECT '2076-11-06 00:00:00.000', 'A1234' union all
SELECT '2078-11-19 00:00:00.000', 'A4567' union all
SELECT '2013-03-14 00:00:00.000', 'A12345'
--I want to get list of the ploicies which are expiring in the current week.
SELECT PolicyNumber,DATEPART(wk, PolicyExpiryDate), DATEPART(wk, GETDATE())
FROM @ploicy
WHERE DATEPART(wk, PolicyExpiryDate) = DATEPART(wk, GETDATE())
AND DATEPART(yy, PolicyExpiryDate) = DATEPART(yy, GETDATE())

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-12 : 06:44:28
better way to write it is

SELECT PolicyNumber,DATEPART(wk, PolicyExpiryDate), DATEPART(wk, GETDATE())
FROM @ploicy
WHERE PolicyExpiryDate > = DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)
AND PolicyExpiryDate < DATEADD(wk,DATEDIFF(wk,0,GETDATE())+1,0)


see

http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html

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

Go to Top of Page
   

- Advertisement -