datetime and seconds sql 2005 - 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)

datetime and seconds sql 2005

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-04-18 : 06:35:21

Hi,

I have a smalldatetime field. When i insert getdate into that fields the seconds are always displayed as 00. How can i insert seconds also?

Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-18 : 07:39:30

don't use smalldatetime, use datetime. smalldatetime does not contain the seconds, the seconds is always 00

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

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-04-18 : 07:40:55

Quote from SQL Server 2005 Books Online "The Database Engine stores smalldatetime values as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. The other 2 bytes store the number of minutes since midnight."
So the accuracy of smalldatetime is limited to minutes, and you should change datatype to datetime of you need more accuracy.

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-04-18 : 07:51:53

hi,

thanks i changed it to datetime but it still doesn't accept the seconds.

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-18 : 07:59:05

how do you assign the datetime to the field ?

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

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-04-18 : 09:08:08

it's the data type

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-18 : 09:14:33

can you show us the code that you use ?

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

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2010-04-18 : 10:39:16

By mistake i changed the datatype in another db with the same table name.

When i now insert getdate to the datetime field it inserts 2010-04-18 16:34:37.070
is it possible to only insert 2010-04-18 16:34:37?

Thanks

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-18 : 10:45:14

[code]
select dateadd(millisecond, -datepart(millisecond, getdate()), getdate()),
convert(smalldatetime, getdate()) + dateadd(second, datepart(second, getdate()), 0)
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources