How can I format the Date in the SQL Table using a SQL query

Hi

I have a SQL table that contains date in this format :-

2006-07-02 16:20:01.000
2006-07-02 16:21:00.000
2006-07-02 16:21:01.000
2006-07-02 16:22:00.000
2006-07-02 16:22:02.000
2006-07-02 16:23:00.000

The date above contains seconds that I dont want, how can I remove those seconds so that the output looks like :-

2006-07-02 16:20:00.000
2006-07-02 16:21:00.000
2006-07-02 16:21:00.000
2006-07-02 16:22:00.000
2006-07-02 16:22:00.000
2006-07-02 16:23:00.000

Your help will be highly appreciated.

Hi,

You can do as..

update tablename set datecolumn = select dateadd(s, -datepart(s,datecolumn), datecolumn)

|||

That will change the data. If you only want to change the display, then try this ;

select replace(convert(varchar(20), columnName, 102), '.', '-') + ' ' + left(convert(varchar(20), columnName, 108), 5) + ':00.000'
from tableName

|||

In case you do not want to update the table you just need to change it in display you can do as..

select dateadd(s, -datepart(s,datecolumn), datecolumn)

from tablename

|||

Hi, thanks for the reply

Here is what i have tried

UPDATE [dbo].[Date_Test] SET Date = SELECT DATEADD(s, -DATEPART(s,Date), Date)

and Im getting the following error, i dont understand whats causing it.

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'SELECT'.

Please help.

|||

Rod Colledge wrote:

That will change the data. If you only want to change the display, then try this ;

select replace(convert(varchar(20), columnName, 102), '.', '-') + ' ' + left(convert(varchar(20), columnName, 108), 5) + ':00.000'
from tableName

Thanks, but I want to change the data, not to display it.

|||

Hi,

You need to remove Select Key word..

UPDATE [dbo].[Date_Test] SET Date = DATEADD(s, -DATEPART(s,Date), Date)

|||

Shallu wrote:

Hi,

You need to remove Select Key word..

UPDATE [dbo].[Date_Test] SET Date = DATEADD(s, -DATEPART(s,Date), Date)

Thanks Shallu, it work perfect