Prev: JDBC Driver issue with SQL Server 2008
Next: File-Permissions in DATA-Folder (Attach / Detach)
From: Muhammad Bilal on 31 Mar 2010 07:16
Hi

I want to update a column of data type datetime in SQL Server 2000.
Let

Col1
23/11/2009
31/12/2009
01/01/2010

i want to substract the 1 from month. i.e.

col1 shoul look like this

23/10/2009
31/11/2009
01/12/2009


Regard,
Muhammad Bilal


From: Harlan Messinger on 31 Mar 2010 07:37
Muhammad Bilal wrote:
> Hi
>
> I want to update a column of data type datetime in SQL Server 2000.
> Let
>
> Col1
> 23/11/2009
> 31/12/2009
> 01/01/2010
>
> i want to substract the 1 from month. i.e.
>
> col1 shoul look like this
>
> 23/10/2009
> 31/11/2009

Error: invalid date!

> 01/12/2009

Anyway, you are looking for the DATEADD function:

DATEADD (datepart , number, date )

where datepart is a code that indicates the unit of time you are adding.
See:

http://msdn.microsoft.com/en-us/library/ms186819.aspx

As for the situation where the resulting date doesn't exist: "If
datepart is month and the date month has more days than the return month
and the date day does not exist in the return month, the last day of the
return month is returned."
From: jgurgul on 31 Mar 2010 08:05
Hi

Take a look at DATEADD.

DECLARE @tbl TABLE (dt datetime)
INSERT INTO @tbl values (GETDATE())

UPDATE @tbl SET dt = DATEADD (dd , -1, dt )

SELECT * FROM @tbl

Jon

"Muhammad Bilal" wrote:

> Hi
>
> I want to update a column of data type datetime in SQL Server 2000.
> Let
>
> Col1
> 23/11/2009
> 31/12/2009
> 01/01/2010
>
> i want to substract the 1 from month. i.e.
>
> col1 shoul look like this
>
> 23/10/2009
> 31/11/2009
> 01/12/2009
>
>
> Regard,
> Muhammad Bilal
>
>
 | 
Pages: 1
Prev: JDBC Driver issue with SQL Server 2008
Next: File-Permissions in DATA-Folder (Attach / Detach)