Prev: order by case value
Next: convert string to datetime
From: DVR on 9 Mar 2010 16:19
Hi All,
----------------------------SQL SERVER 2008 Database-----

The AP clerks forgot to change the year from 2009 to 2010, so they did
lots of data entry with invoice dates instead of 2010, they added 2009
and the date field looks like this "2009-01-16 00:00:00.000", is there
a way to replace 2009 with 2010.

Please help me and thanks in advance,
Ramesh
From: Tom on 9 Mar 2010 17:14
On Mar 9, 4:19 pm, DVR <dvramesh...(a)gmail.com> wrote:
> Hi All,
> ----------------------------SQL SERVER 2008 Database-----
>
> The AP clerks forgot to change the year from 2009 to 2010, so they did
> lots of data entry with invoice dates instead of 2010, they added 2009
> and the date field looks like this "2009-01-16 00:00:00.000", is there
> a way to replace 2009 with 2010.
>
> Please help me and thanks in advance,
> Ramesh

DECLARE @StartDate DATETIME = '2004-07-31 00:00:00.000';
DECLARE @EndDate DATETIME = '2005-01-26 09:17:08.637';

SELECT CAST('2010' + SUBSTRING(CONVERT(VARCHAR(30), [ModifiedDate],
121), 5, 50) AS DATETIME)
,ModifiedDate
FROM [AdventureWorks2008].[HumanResources].[Employee]
WHERE ModifiedDate BETWEEN @StartDate AND @EndDate;

In your case

UPDATE sometable
set datefield = CAST('2010' + SUBSTRING(CONVERT(VARCHAR(30),
[ModifiedDate], 121), 5, 50) AS DATETIME)
where datefield BETWEEN @StartDate AND @EndDate;

From: jgurgul on 9 Mar 2010 17:27
Hi Ramesh

UPDATE tbl SET col = DATEADD (y , 1, col) --adding 1 year
WHERE YEAR(col) = 2009 AND OtherCriteria


DATEADD (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms186819.aspx

Jon

"DVR" wrote:

> Hi All,
> ----------------------------SQL SERVER 2008 Database-----
>
> The AP clerks forgot to change the year from 2009 to 2010, so they did
> lots of data entry with invoice dates instead of 2010, they added 2009
> and the date field looks like this "2009-01-16 00:00:00.000", is there
> a way to replace 2009 with 2010.
>
> Please help me and thanks in advance,
> Ramesh
> .
>
From: Bob Barrows on 9 Mar 2010 17:26
DVR wrote:
> Hi All,
> ----------------------------SQL SERVER 2008 Database-----
>
> The AP clerks forgot to change the year from 2009 to 2010, so they did
> lots of data entry with invoice dates instead of 2010, they added 2009
> and the date field looks like this "2009-01-16 00:00:00.000", is there
> a way to replace 2009 with 2010.
>
Tom showed you one way to do it (another way would be to use DATEADD -
assuming this is a datetime column rather than varchar), but I'm curious
as to how you intend to identify the mistaken entries. I'm sure there
are records in your table that legitimately have 2009 invoice dates ...

--
HTH,
Bob Barrows


From: Plamen Ratchev on 9 Mar 2010 17:30
You can simply add one year to these dates:

UPDATE Table
SET date = DATEADD(YEAR, 1, date)
WHERE <predicate to filter only rows that should be updated>;

If all 2009 dates should be updated, then it can look like this:

UPDATE Table
SET date = DATEADD(YEAR, 1, date)
WHERE date >= '20090101'
AND date < '20100101';

--
Plamen Ratchev
http://www.SQLStudio.com
 |  Next  |  Last
Pages: 1 2
Prev: order by case value
Next: convert string to datetime