[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

sql duplicates

Posted on 2011-10-31
6
Medium Priority
?
294 Views
Last Modified: 2012-05-12
I have a requirement that says, update a flag with an effective date between 80 and 14 days in the past.
Does the following query look correct to you guys.

BEGIN TRANSACTION
	UPDATE 
		[Storage_Table]
	SET 
		[FLAG] =
		CASE 
			WHEN (DATEPART(DAY, [START_DATE]) BETWEEN 80 AND 14)  THEN 'Y'
			ELSE 'N'
		END
COMMIT TRANSACTION

Open in new window

0
Comment
Question by:aneilg
  • 3
  • 2
6 Comments
 
LVL 1

Expert Comment

by:dhruvpratap
ID: 37055795
I think you should use the DATEDIFF() function here.

DATEPART only return the date pat of the date i.e. day of the month (between 1 to 31)
0
 
LVL 5

Accepted Solution

by:
mcs0506 earned 500 total points
ID: 37055814
Hi
Try following query

BEGIN TRANSACTION
      UPDATE
            [Storage_Table]
      SET
            [FLAG] =
            CASE
                  WHEN
                        [START_DATE]>= DATEADD(DAY,-80,GETDATE()) AND      
                        [START_DATE]<= DATEADD(DAY,-14,GETDATE())
                  THEN 'Y'
                  ELSE 'N'
            END
COMMIT TRANSACTION


Regarsd


Dani
0
 
LVL 1

Assisted Solution

by:dhruvpratap
dhruvpratap earned 500 total points
ID: 37055862
BEGIN TRANSACTION
	UPDATE 
		[Storage_Table]
	SET 
		[FLAG] =
		CASE 
			WHEN (DATEDIFF(DAY, [START_DATE], SYSDATE) BETWEEN 80 AND 14)  THEN 'Y'
			ELSE 'N'
		END
COMMIT TRANSACTION

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:aneilg
ID: 37055914
thanks guys thats perfect.
0
 

Author Comment

by:aneilg
ID: 37055943
just looking at the differences between DATEADD,DATEDIFF,DATEPART.
0
 

Author Closing Comment

by:aneilg
ID: 37055950
fantastic
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question