How to store only the time part of a date?

[Jonas Bergman]

Hi

I would like to store only the timepart of a date in a datetime field in SQL
Server. I know that is is possible, I have seen it being done from C++, but
i don´t have the source code for that project.

Any ideas?

/Jonas Bergman

 

[Sahil Malik]

You've seen that happen in C++ on a Sql Server? I've been a C++ programmer
for 8 years and I haven't seen it. Plus it's more of a database thing rather
than a programming language thing IMHO.

I don't think, there is any way currently to do this in Sql 2000 in a
datetime field. Even if you set everything to 0 plus time, you will still
get a date portion.
In SQL 2005, you can define your own UDT for this, but in SQL 2000 you will
have to trim off date after retreival.

Or of course store it as a string and Convert in C#, thats another way of
doing it.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

 

[Jonas Bergman]

Ok, I got an answer from the C++ guy that fixed this a few years ago.

Store the time with the date 30/12/1899.

I tried this and in the enterprise manager only the time part is
displayed.

This also works from ADO.NET.

The time is however stored with a date as well, and a Select-clause like
this "WHERE MyTime = '06:00:00'" doesn't work.

If you request the records using the query analyzer, the date portion is
displayed as well, but at least it looks nice in the enterprise manager.

I have realized that dates will have to be stored as well, but at least
I am satisfied to have figured out what date to use to get the datetime
field to look like a time field...

 

[Sahil Malik]

Wow thats neat. When a dataset fills it, does it look like a "time" or does
it still look like a "datetime" ?

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

 

[Jonas Bergman]

i did a quick test and all that was displayed in the datagrid was the
date... :-(

But if you can make it work, please let me know.

 

[Sahil Malik]

I thought so Jonas .. that would have been the behavior.

I think the solution would be centered around trimming the date portion. Why
don't you do something like this --

Create your own value type, call it "Time".
Implement implicit conversions from string and datetime - when it accepts
something that looks like a datetime, it returns you a "time".

Cast your column in question to this "Time" datatype you just created.

That would be simple and elegant IMHO.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

 

[Jonas Bergman]

One more thing, that I just found out:

As I said earlier, storing the date as 30/12/1899 stops the date part
from being displayed.

If you use the date 1/1/1900 instead, the date part is displayed in the
enterprise manager, but on the other hand, it is possible to use a
select clause like this: "WHERE fld_mytime = '23:00'"

At least this works in the query analyzer, I haven't tried it from
within ADO.NET.

 

[Sahil Malik]

And BTW, my previously recommended solution has a few limitations, you won't
be able to use that in the "Expression" property, or cast it to/from an
object and expect auto conversions .. i.e. you can't do code like.

object o = "31/12/1988 01:10:12 PM" ;
Time t = o ; // can't do this and get reliable results too.

This is so because you can't implement Implicit/Explicit conversions from
System.Object .. and also you can't inherit from System.ValueType (.NET
specification limitation - which I don't know the reasoning behind and no
one has been able to give me a better answer that .. Hey it's in the spec).

Anyway hope that helps.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik