Datetime and the missing milliseconds

another datetime problem...

When I send a .net Datetime as a param to a SQL Server sproc, the milliseconds disappear.

I am returning a lastUpdatedDate field from a table in SQL Server to my asp.net page along with updateable table data. The user makes their updates and then submits the form which sends the data to a sproc which makes the update. The procedure makes a check that the lastUpdatedDate submitted back from the form is still the same as the one in the database (to guard against lost updates)

However, when the lastUpdatedDate gets back into SQL server it is missing the milliseconds component - they have been set to 000. I've been tearing my hair out trying to find the point at which the milliseconds disappear and why - but to no avail.

The way I'm doing it is storing the lastUpdatedDate in a .net datetime, passing this into viewstate and then when the user submits the update, retrieving it from ViewState back into the datetime variable and then submitting it to the sproc using a parameter of SqlDbType.Datetime

I have put bits of debug code at every point in the .net processing to look at the datetime and the milliseconds are always there... its only when the value gets picked up in SQL Server that they have disappeared

Any ideas?

MaracatuIs the data type in the data base table DateTime or SmallDateTime? If it's SmallDateTime that might be causing the truncation of the milliseconds (and seconds).|||Its datetime.
The milliseconds go missing when the datetime is retrieved from ViewState after a round trip to the client (bizarrely if the datetime is placed in Viewstate and immediately retrieved and sent to the database no problem occurs).

I have now solved the problem by storing a String value of the datetime, including the milliseconds in ViewState rather than puting the datetime value in there directly.|||Interesting, I now recall having a similar problem with disappearing milliseconds. It wasn't a concern and so I didn't check up on it but I bet that was the reason.

Probably when a datetime is placed into ViewState it serializes it as a string but without milliseconds. But it doesn't actually get serialized until just before the page it sent back to the client so if you stick it into ViewState and pull it back out it's still the original datetime.

Date/Time formatting

Hi all, I'm hoping this is an easy solution . . .
How can I format the system date: getdate() as mm/dd/yyyy hh:mi am?
Am I missing something? I am fairly new to sql server . . .
I would use datepart, but I cannot find a way to display the time like
I have shown.
Your help is appreciated.
Thank you and have a great night,
Ryan<ryan.mclean@.gmail.com> wrote in message
news:1130715275.732555.138960@.g43g2000cwa.googlegroups.com...
> Hi all, I'm hoping this is an easy solution . . .
> How can I format the system date: getdate() as mm/dd/yyyy hh:mi am?
> Am I missing something? I am fairly new to sql server . . .
> I would use datepart, but I cannot find a way to display the time like
> I have shown.
> Your help is appreciated.
> Thank you and have a great night,
> Ryan
>
The way your dates and times are displayed is controlled by your client
application, not by SQL Server. Format the value client-side.
If you must control the format from the server then you'll have to return a
string instead of a DATETIME. Take a look at the CONVERT function in Books
Online. CONVERT allows you to convert DATETIME to a string in any one of
various formats.
David Portas
SQL Server MVP
--|||<ryan.mclean@.gmail.com> wrote in message
news:1130715275.732555.138960@.g43g2000cwa.googlegroups.com...
> Hi all, I'm hoping this is an easy solution . . .
> How can I format the system date: getdate() as mm/dd/yyyy hh:mi am?
> Am I missing something? I am fairly new to sql server . . .
> I would use datepart, but I cannot find a way to display the time like
> I have shown.
> Your help is appreciated.
> Thank you and have a great night,
> Ryan
>
The way your dates and times are displayed is controlled by your client
application, not by SQL Server. Format the value client-side.
If you must control the format from the server then you'll have to return a
string instead of a DATETIME. Take a look at the CONVERT function in Books
Online. CONVERT allows you to convert DATETIME to a string in any one of
various formats.
David Portas
SQL Server MVP
--|||Hi David,
First, thanks for posting a reply . . .
I am displaying the data in an asp.net datagrid, so it is actually more
efficient if I format the data on the database server in my sql
statement.
I have looked at the convert function, and I don't see where I can
format the data how I have specified. In ocacle, I would use to_date
and it would be done.
Thanks for trying, but I need a little more guidance then suggesting a
function.
Ryan|||I'm surprised that you think it's more efficient to do formatting in
the database than in ADO. Usually formatting is done client side
precisely to avoid putting that overhead on the database. ADO provides
date formatting through the ToString method for that purpose.
Aside from efficiency, doing it at the client has the advantage of
being user-configurable. Apparently you want to force all your users to
conform to your preference on how they should view dates. Also, if you
return a formatted string in the form you suggested then the user won't
be able to sort on it.
To do it in the database would look something like this:
SELECT CONVERT(CHAR(10),CURRENT_TIMESTAMP,101)+
'
'+RIGHT(CONVERT(VARCHAR,CURRENT_TIMESTAM
P,0),8)
David Portas
SQL Server MVP
--|||>> I have looked at the convert function, and I don't see where I can format
SELECT CONVERT( VARCHAR, GETDATE(), 101 ) +
RIGHT( CONVERT( VARCHAR, GETDATE(), 100 ), 8 )
Anith|||Hi again,
David, that is a good point and it would probably be more applicable if
it were a distributed app (it's just an admin tool that we will use).
I just don't see the advantage of pulling back a data set, then looping
through every record and messing with each item individually when I can
have the database engine do all the work, then I just have to display
it.
Anyway, always a learning process. I really appreciate your posts.
Also, a thank you to Anith.
Thanks and have a great day,
Ryan