select only date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

select only date

Author

Topic

muvvasiva
Starting Member

14 Posts

Posted - 2009-06-24 : 02:05:37

hi all

i have to eliminate milli seconds from the following data.

LastModified
============
2009/06/01 12:36:39.958
2007/11/16 15:59:20.312
2007/12/18 12:24:52.748
2007/11/16 15:59:20.234
2008/04/15 10:20:46.709
2008/04/15 10:20:46.741
2008/07/17 8:58:59.822
2007/12/10 15:32:0.421

i have to select from above column as follows
LastModified
=============
2009/06/01 12:36:39
2007/11/16 15:59:20
2007/12/18 12:24:52
2007/11/16 15:59:20
2008/04/15 10:20:46
2008/04/15 10:20:46
2008/07/17 8:58:59
2007/12/10 15:32:0

here, i have to eliminate fraction of seconds from data
pls help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-24 : 02:08:49

select dateadd(millisecond, -datepart(millisecond, LastModified), LastModified)

KH
[spoiler]Time is always against us[/spoiler]

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-06-24 : 02:18:30

Hi, see this it is useful for u

http://www.sql-server-helper.com/tips/date-formats.aspx

saran_d28
Starting Member

36 Posts

Posted - 2009-06-24 : 02:55:03

Hi,

You can use the conver function.

select convert(char(20),'2009/06/01 12:36:39.958', 120)

Thanks
Saravanan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-24 : 02:56:24

quote:
Originally posted by saran_d28

Hi,

You can use the conver function.

select convert(char(20),'2009/06/01 12:36:39.958', 120)

Thanks
Saravanan

that will be changing the data type of the result. Unless you convert it back to datetime.

KH
[spoiler]Time is always against us[/spoiler]

muvvasiva
Starting Member

14 Posts

Posted - 2009-06-24 : 05:33:24

Thank you very much for all who replied with great concern

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources