[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

SmallDateTime

Rahul

3/13/2007 5:40:00 AM

Friends
I have face very silly problem.
I have insert following rows and find the result is totally
different why?, Is sql server 2000 has not consider time correctly.


Create Table #temp
(
ColA VarChar(4),
ColB SmallDateTime
)


Insert Into #temp Values('A', '2007-03-11 17:59:59')
Insert Into #temp Values('B', '2007-03-10 17:59:59')
Insert Into #temp Values('C', '2007-03-10 18:00:00')
Insert Into #temp Values('D', '2007-03-10 18:00:01')
Insert Into #temp Values('E', '2007-03-11 18:01:00')


ColA ColB
---- ------------------------------------------------------
A 2007-03-11 18:00:00
B 2007-03-10 18:00:00
C 2007-03-10 18:00:00
D 2007-03-10 18:00:00
E 2007-03-11 18:01:00

(5 row(s) affected)
9 Answers

Kalen Delaney

3/13/2007 6:27:00 AM

0

Hi Rahul

Smalldatetime only has an accuracy to the nearest minute. Please read about
datetime and smalldatetime in the Books Online
Your values will be rounded to the nearest minute.

--
HTH
Kalen Delaney, SQL Server MVP
http://s...


"Rahul" <verma.career@gmail.com> wrote in message
news:1173764424.019470.124090@v33g2000cwv.googlegroups.com...
> Friends
> I have face very silly problem.
> I have insert following rows and find the result is totally
> different why?, Is sql server 2000 has not consider time correctly.
>
>
> Create Table #temp
> (
> ColA VarChar(4),
> ColB SmallDateTime
> )
>
>
> Insert Into #temp Values('A', '2007-03-11 17:59:59')
> Insert Into #temp Values('B', '2007-03-10 17:59:59')
> Insert Into #temp Values('C', '2007-03-10 18:00:00')
> Insert Into #temp Values('D', '2007-03-10 18:00:01')
> Insert Into #temp Values('E', '2007-03-11 18:01:00')
>
>
> ColA ColB
> ---- ------------------------------------------------------
> A 2007-03-11 18:00:00
> B 2007-03-10 18:00:00
> C 2007-03-10 18:00:00
> D 2007-03-10 18:00:00
> E 2007-03-11 18:01:00
>
> (5 row(s) affected)
>


Jay Konigsberg

3/13/2007 6:53:00 AM

0

From BOL

"smalldatetime
Date and time data from January 1, 1900, through June 6, 2079, with accuracy
to the minute."



"Rahul" <verma.career@gmail.com> wrote in message
news:1173764424.019470.124090@v33g2000cwv.googlegroups.com...
> Friends
> I have face very silly problem.
> I have insert following rows and find the result is totally
> different why?, Is sql server 2000 has not consider time correctly.
>
>
> Create Table #temp
> (
> ColA VarChar(4),
> ColB SmallDateTime
> )
>
>
> Insert Into #temp Values('A', '2007-03-11 17:59:59')
> Insert Into #temp Values('B', '2007-03-10 17:59:59')
> Insert Into #temp Values('C', '2007-03-10 18:00:00')
> Insert Into #temp Values('D', '2007-03-10 18:00:01')
> Insert Into #temp Values('E', '2007-03-11 18:01:00')
>
>
> ColA ColB
> ---- ------------------------------------------------------
> A 2007-03-11 18:00:00
> B 2007-03-10 18:00:00
> C 2007-03-10 18:00:00
> D 2007-03-10 18:00:00
> E 2007-03-11 18:01:00
>
> (5 row(s) affected)
>


Uri Dimant

3/13/2007 7:50:00 AM

0

Hi
Change SMALLDATETINE to DATETIME to get what you want. It is up to minute
accuracy with SMALLDATETIME datatype





"Rahul" <verma.career@gmail.com> wrote in message
news:1173764424.019470.124090@v33g2000cwv.googlegroups.com...
> Friends
> I have face very silly problem.
> I have insert following rows and find the result is totally
> different why?, Is sql server 2000 has not consider time correctly.
>
>
> Create Table #temp
> (
> ColA VarChar(4),
> ColB SmallDateTime
> )
>
>
> Insert Into #temp Values('A', '2007-03-11 17:59:59')
> Insert Into #temp Values('B', '2007-03-10 17:59:59')
> Insert Into #temp Values('C', '2007-03-10 18:00:00')
> Insert Into #temp Values('D', '2007-03-10 18:00:01')
> Insert Into #temp Values('E', '2007-03-11 18:01:00')
>
>
> ColA ColB
> ---- ------------------------------------------------------
> A 2007-03-11 18:00:00
> B 2007-03-10 18:00:00
> C 2007-03-10 18:00:00
> D 2007-03-10 18:00:00
> E 2007-03-11 18:01:00
>
> (5 row(s) affected)
>


kuNDze

3/13/2007 11:26:00 AM

0

from BOL:
smalldatetime values that are 29.998 seconds or less are rounded down
to the nearest minute; values of 29.999 seconds or more are rounded up
to the nearest minute.

Rahul ra?e:
> Friends
> I have face very silly problem.
> I have insert following rows and find the result is totally
> different why?, Is sql server 2000 has not consider time correctly.
>
>
> Create Table #temp
> (
> ColA VarChar(4),
> ColB SmallDateTime
> )
>
>
> Insert Into #temp Values('A', '2007-03-11 17:59:59')
> Insert Into #temp Values('B', '2007-03-10 17:59:59')
> Insert Into #temp Values('C', '2007-03-10 18:00:00')
> Insert Into #temp Values('D', '2007-03-10 18:00:01')
> Insert Into #temp Values('E', '2007-03-11 18:01:00')
>
>
> ColA ColB
> ---- ------------------------------------------------------
> A 2007-03-11 18:00:00
> B 2007-03-10 18:00:00
> C 2007-03-10 18:00:00
> D 2007-03-10 18:00:00
> E 2007-03-11 18:01:00
>
> (5 row(s) affected)

neeju

3/13/2007 11:29:00 AM

0


See BOL for smalldatetime.

use datetime datatype instead or cast the values for exact output.

NJ


On Mar 13, 10:40 am, "Rahul" <verma.car...@gmail.com> wrote:
> Friends
> I have face very silly problem.
> I have insert following rows and find the result is totally
> different why?, Is sql server 2000 has not consider time correctly.
>
> Create Table #temp
> (
> ColA VarChar(4),
> ColB SmallDateTime
> )
>
> Insert Into #temp Values('A', '2007-03-11 17:59:59')
> Insert Into #temp Values('B', '2007-03-10 17:59:59')
> Insert Into #temp Values('C', '2007-03-10 18:00:00')
> Insert Into #temp Values('D', '2007-03-10 18:00:01')
> Insert Into #temp Values('E', '2007-03-11 18:01:00')
>
> ColA ColB
> ---- ------------------------------------------------------
> A 2007-03-11 18:00:00
> B 2007-03-10 18:00:00
> C 2007-03-10 18:00:00
> D 2007-03-10 18:00:00
> E 2007-03-11 18:01:00
>
> (5 row(s) affected)

EMartinez

3/13/2007 11:34:00 AM

0

On Mar 13, 12:40 am, "Rahul" <verma.car...@gmail.com> wrote:
> Friends
> I have face very silly problem.
> I have insert following rows and find the result is totally
> different why?, Is sql server 2000 has not consider time correctly.
>
> Create Table #temp
> (
> ColA VarChar(4),
> ColB SmallDateTime
> )
>
> Insert Into #temp Values('A', '2007-03-11 17:59:59')
> Insert Into #temp Values('B', '2007-03-10 17:59:59')
> Insert Into #temp Values('C', '2007-03-10 18:00:00')
> Insert Into #temp Values('D', '2007-03-10 18:00:01')
> Insert Into #temp Values('E', '2007-03-11 18:01:00')
>
> ColA ColB
> ---- ------------------------------------------------------
> A 2007-03-11 18:00:00
> B 2007-03-10 18:00:00
> C 2007-03-10 18:00:00
> D 2007-03-10 18:00:00
> E 2007-03-11 18:01:00
>
> (5 row(s) affected)

For 'smalldatetime,' the precision is in minutes (so it will round to
the nearest minute). I would suggest using 'datetime' here instead,
it's precision is to 3.33 milliseconds.

Regards,

Enrique Martinez
Sr. SQL Server Developer

Adi Cohn

3/13/2007 11:44:00 AM

0

On Mar 13, 7:40 am, "Rahul" <verma.car...@gmail.com> wrote:
> Friends
> I have face very silly problem.
> I have insert following rows and find the result is totally
> different why?, Is sql server 2000 has not consider time correctly.
>
> Create Table #temp
> (
> ColA VarChar(4),
> ColB SmallDateTime
> )
>
> Insert Into #temp Values('A', '2007-03-11 17:59:59')
> Insert Into #temp Values('B', '2007-03-10 17:59:59')
> Insert Into #temp Values('C', '2007-03-10 18:00:00')
> Insert Into #temp Values('D', '2007-03-10 18:00:01')
> Insert Into #temp Values('E', '2007-03-11 18:01:00')
>
> ColA ColB
> ---- ------------------------------------------------------
> A 2007-03-11 18:00:00
> B 2007-03-10 18:00:00
> C 2007-03-10 18:00:00
> D 2007-03-10 18:00:00
> E 2007-03-11 18:01:00
>
> (5 row(s) affected)

This is because you are using smalldatetime data type. Smalldatetime
has an accuracy of the minute. If you need a better accuracy, then
you should use datetime data type, which has an accuracy of one three-
hundredth of a second.

Adi

Rahul

3/13/2007 12:24:00 PM

0

On Mar 13, 11:52 am, "JayKon" <s...@nospam.org> wrote:
> From BOL
>
> "smalldatetime
> Date and time data from January 1, 1900, through June 6, 2079, with accuracy
> to the minute."
>
> "Rahul" <verma.car...@gmail.com> wrote in message
>
> news:1173764424.019470.124090@v33g2000cwv.googlegroups.com...
>
>
>
> > Friends
> > I have face very silly problem.
> > I have insert following rows and find the result is totally
> > different why?, Is sql server 2000 has not consider time correctly.
>
> > Create Table #temp
> > (
> > ColA VarChar(4),
> > ColB SmallDateTime
> > )
>
> > Insert Into #temp Values('A', '2007-03-11 17:59:59')
> > Insert Into #temp Values('B', '2007-03-10 17:59:59')
> > Insert Into #temp Values('C', '2007-03-10 18:00:00')
> > Insert Into #temp Values('D', '2007-03-10 18:00:01')
> > Insert Into #temp Values('E', '2007-03-11 18:01:00')
>
> > ColA ColB
> > ---- ------------------------------------------------------
> > A 2007-03-11 18:00:00
> > B 2007-03-10 18:00:00
> > C 2007-03-10 18:00:00
> > D 2007-03-10 18:00:00
> > E 2007-03-11 18:01:00
>
> > (5 row(s) affected)- Hide quoted text -
>
> - Show quoted text -

Jaykon,
I know about the accuracy of smalldatetime datatype.
But in bol, there is not mentation about time, ie time is change.

Rahul

Jay Konigsberg

3/13/2007 3:56:00 PM

0

> I know about the accuracy of smalldatetime datatype.
> But in bol, there is not mentation about time, ie time is change.

Are you asking why the time is rounded and not truncated? If so, it's just
the way Microsoft did it.