Dates get rounded when passed to Sql Server with time component

[Stanley Omega]

Hello,

I needed to get the last day in the month. I used a very basic formula which
essentially subtracted 3 seconds from the 1st day of the following month.
This gave me a string like "#7/31/2006 11:59:57PM".

I then passed this to an sql server parameter as its value for a query.

..Item("@PeriodEnd").Value = CDate(reports.SAPR(i).PeriodEnd)

I found i kept getting a value in the database of 08.01.2006 or 1st of
august.????

So i debug to the line

..Item("@PeriodEnd").Value = CDate(reports.SAPR(i).PeriodEnd)

and use the immediate windown to get the value

?CDate(reports.SAPR(i).PeriodEnd)

and it still reads

"#7/31/2006 11:59:57PM".

I have fixed my bug by rounding out the date before it even gets to the
query so im not interested in fixes but rather would like to find out why
the date is rounding in the first place between .net 2.0 and sql server.

This may affect me in other ways so please advise or provide a link to this
intended or unintended behaviour. I did not know this gets rounded. Did you?

Thanks

stano

 

[Guest]

You might learn some more reading the following document
http://msdn2.microsoft.com/en-us/library/ms187819.aspx. Depending on the
date-datatype used, Sql Server can only store up to 3 milliseconds or 1
minute accuracy. Perhaps you're using a smalldatetime field which can not
contain any seconds?

 

[Stanley Omega]

Thanks Michael,

Thats would be precisely the problem. Isee the rate for that page is
averages only 1. I guess the whole rest of the world already knew that.

Thanks

stano