save the time only to SQLSvr2000 db using asp.net and datagrid

Question

Post reply

save the time only to SQLSvr2000 db using asp.net and datagrid

Anne Marie

SSC Rookie

Points: 32
More actions
May 12, 2006 at 10:13 am

#103161

I would like to save only the time in a date/time field in SQL Server 2000 database.
I have a datagrid where I am displaying the date in one column and the time arrived in another column. I want to save this to the database in the same format. Also, the user will be able to edit the date and time fields in the datagrid.
Does anyone have some asp.net code to do this? Thanks,

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sean Grebey-262535 SSCommitted Points: 1724 More actions · Answer 1

You can get just the Time part of a DateTime with a ToLongTimeString or ToShortTimeString. e.g.:

Dim MyTime as DateTime

MyTime = MyDateTime.ToLongTimeString()

Then the SQL is easy...

"INSERT INTO MyTable (MyTime) VALUES(@MyTime)"

Where @MyTime is a param

SQLCommand.Parameters.Add("@MyTime",MyTime)

ToLongTimeString

Anne Marie SSC Rookie Points: 32 More actions · Answer 2

The following code isn't working, meaning no record is saved to the database:

dr.rec_checkin_time = Now().ToShortTimeString

This doesn't work either:

dr.rec_checkin_time = CType(Now().ToShortTimeString, DateTime)

And neither does this

Dim

MyTime As DateTime

MyTime = Now().ToLongTimeString()

dr.rec_checkin_time = MyTime

This works, however, it stores the current date and time.

dr.rec_checkin_time = Now()

I'm using a dataset and the insert statement runs after the user adds a row via the data grid. What am I missing here? Does anyone know? I would even be happy if I could store 1/1/1900 and the current time.

Thanks,

Sean Grebey-262535 SSCommitted Points: 1724 More actions · Answer 3

I don't know what dr.rec_checkin_time is, but I would guess it is a datetime field of some sort?

Try:

dr.rec_checkin_time = CDate(Now().ToShortTimeString )

Anne Marie SSC Rookie Points: 32 More actions · Answer 4

Yes, it is a datetime field, a dataset datarow.

Your code above works, however, the row is not being saved to the database.

I'm getting the following error: SQLDateTimeOverflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

Do I need to include a default date like 1/1/1900 to the time so that it will save to the database. Or is there a way to make SQL do that part of it for me?

Thanks,

Sean Grebey-262535 SSCommitted Points: 1724 More actions · Answer 5

When you look at:

dr.rec_checkin_time

What value is in there before the Insert? Put a break point and a watch on it after you set it.

Anne Marie SSC Rookie Points: 32 More actions · Answer 6

Dim MyTime As DateTime

Before MyTime is populated it is #12:00:00 AM#

After the line of code:

MyTime = CDate(Now().ToShortTimeString)

it is #10:44:00 AM#

Sean Grebey-262535 SSCommitted Points: 1724 More actions · Answer 7

Huh, you shouldn't have any trouble storing that in a SQL DataTime. Try appending 1/1/1900 to it before you convert it back to a Date and see what happens.

Anne Marie SSC Rookie Points: 32 More actions · Answer 8

Yep, that works fine. I can now save the time, but not without 1/1/1900 in front of it. Thanks for your help!