How to update column in SQL Server 30 days from stored date on form load?

Question

I want to take the rows with a createDate 30 days old and change the bit value IsExpired to 1. How do I accomplish this?

Here is the query I have tried:

UPDATE [mydb].[dbo].[mytable] 
SET IsExpired = 1 
WHERE CreateDate > (time, SYSDATETIME(GETDATE(CreateDate)+30))

The CreateDate column has a datetime string stored within it from a C# project. For example the value stored in the first row is 2013-05-29 14:59:48.000. When I execute it in SQL I get an error

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','.

Is this SQL or C#.NET. This looks like SQL. This issue is what is (time, XXX) — logixologist, Jul 01 '13 at 18:47
if its sql try UPDATE [mydb].[dbo].[mytable] SET IsExpired=1 WHERE CreateDate > dateadd(dd, 30, getdate()) — logixologist, Jul 01 '13 at 18:49
i get `0 rows affected` is this because `IsExpired` is a bit datatype? should I use int instead? — Skullomania, Jul 01 '13 at 18:55

score 4 · Answer 1 · edited Jul 01 '13 at 19:21

4

Try something like this:

UPDATE [mydb].[dbo].[mytable] 
SET IsExpired = 1 
WHERE CreateDate < dateadd(dd, -30, getdate())

edited Jul 01 '13 at 19:21

marc_s

732,580
175
1,330
1,459

answered Jul 01 '13 at 18:53

logixologist

3,694
4
28
46

i get `0 rows affected` is this because `IsExpired` is a bit datatype? should I use int instead? – Skullomania Jul 01 '13 at 18:57

score 2 · Answer 2 · edited May 23 '17 at 10:32

2

Try below Query:

UPDATE [mydb].[dbo].[mytable] SET IsExpired=1 WHERE CreateDate < DATEADD(dd, -30, GETDATE())

Also this post will help you to know about DATEADD().

First I assumed that Skullomania is trying to get 30 days of CreateDate. Later from Scott Chamberlain comments, I got the point of user.

edited May 23 '17 at 10:32

Community

1
1

answered Jul 01 '13 at 18:54

Praveen

55,303
33
133
164

You are comparing it with itself, should one of those `CreateDate`s be a `GetDate()`? – Scott Chamberlain Jul 01 '13 at 19:02
@ScottChamberlain I thought Skullomania is comparing with CreateDate -30 day from CreateDate. – Praveen Jul 01 '13 at 19:06
2

I think Skullomania had no idea what he was doing and made a guess on what to do, that guess was wrong and he came here to find out the correct way to do it. The use of [`SYSDATETIME()`](http://msdn.microsoft.com/en-us/library/bb630353.aspx) makes me think he really just wanted to use `GetDate()` with a `DateAdd()`, If you logiclly step through the code you gave him you just said `Where 1=1 and CreateDate is not null` as a date minus 30 days will always be less than the original date (unless it is null)! – Scott Chamberlain Jul 01 '13 at 19:12
@ScottChamberlain Thanks for insight of the question. – Praveen Jul 01 '13 at 19:24

score 2 · Answer 3 · answered Jul 01 '13 at 18:56

The following should match where the CreateDate field is less than 30 days prior to the current date of your SQL server. You may want to use GETUTCDATE() if you store your times in UTC.

UPDATE [mydb].[dbo].[mytable] SET IsExpired=1 
    WHERE CreateDate < DATEADD(DAY, -30, GETDATE())

score 0 · Answer 4 · answered Jul 02 '13 at 11:40

0

You can try:

UPDATE [mydb].[dbo].[mytable] 
SET IsExpired=1 
WHERE CreateDate = DATEADD(MONTH, -1, GETDATE())

answered Jul 02 '13 at 11:40

Mohit

115
2
12

How to update column in SQL Server 30 days from stored date on form load?

4 Answers4