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.
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 ExamplePolicy Expiry Date Policy Number------------------- ----------------2012-03-14 00:00:00.000 A1432089-10-31 00:00:00.000 A3262076-11-06 00:00:00.000 A12342078-11-19 00:00:00.000 A45672012-12-01 00:00:00.000 A12345I 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 @ploicySELECT '2012-03-14 00:00:00.000', 'A143' union allSELECT '2089-10-31 00:00:00.000', 'A326' union allSELECT '2076-11-06 00:00:00.000', 'A1234' union allSELECT '2078-11-19 00:00:00.000', 'A4567' union allSELECT '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 @ploicyWHERE DATEPART(wk, PolicyExpiryDate) = DATEPART(wk, GETDATE())AND DATEPART(yy, PolicyExpiryDate) = DATEPART(yy, GETDATE())--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-12 : 06:44:28
|
better way to write it isSELECT PolicyNumber,DATEPART(wk, PolicyExpiryDate), DATEPART(wk, GETDATE())FROM @ploicyWHERE PolicyExpiryDate > = DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)AND PolicyExpiryDate < DATEADD(wk,DATEDIFF(wk,0,GETDATE())+1,0) seehttp://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|