Saving Time to SQL Server

[garyfis]

I am trying to save a time to a datatime field in SQL Server 2000, but when I do, I get the following error:

 

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

 

I have tried so many combinations of conversions and just hard coding a time it, to no avail. When I harded coded a time in, I still got the date put in.

 

I would appreciate any help that can be given. The time is going into a LastUpdated field, so there is no user input of the time.

 

Thanks...

[millenniasp]

what is the code that you have currently?

[garyfis]

This code gave me only the time: #09:00:00 AM# which I wanted, but the DB would not except it. This is what I initially had:

 

workrowMasterName("LastUpdatedTime") = CDate(FormatDateTime(Now, DateFormat.ShortTime))

 

 

This is what I just found will work:

 

workrowMasterName("LastUpdatedTime") = CDate("1899-12-30 " & FormatDateTime(Now, DateFormat.ShortTime))

 

 

I found that if you put in the SQL Server default date, it will not show up. There has got to be a better way.

 

Thanks for your response.

[millenniasp]

Yeah, I just did some research, and it would be a pain to just code for the time. I know that you can code for just the minute/hour/second but not a combination of the 3

 

the only thing I could come up with in SQL was this

 

select CAST(DATEPART(hh, GETDATE()) as varchar) + ':' + CAST(DATEPART(mi, GETDATE()) as varchar) + ':' + CAST(DATEPART(ss, GETDATE()) as varchar) AS TimeHour

[garyfis]

SQL Server doesn't seem to allow just a time to be put in a DATETIME field. That's why I had to add the SQL Server default date, so that I could get only the time to appear in the field (without the date). I also tried hard coding in a time and found that then the current date was automatically entered. Perhaps the only answer to this is to hard code the default date in so that during the INSERT or UPDATE the current date isn't put in. Then the default date is hidden and only the time is visible.