Separate DateTime filed into Date and Time

[cybercop23]

Hi everyone.

I'm loading 100s of thousands of records weekly that have a filed as DATETIME (12/26/2005 11:26:00). I would like to separate this one filed into two. A date field and a time field. I can do that using the cast function. My problem is that I also need to create an index using this date field and I think it is not very efficient for such high number of records to make this date field be a nvchar or any other type of charachter field. The data will be retrieved from an ASP page, where the user will enter the date they are looking for or choose it from an ActiveX calendar control. Any ideas on how to split this field and make it the most efficient for creating an index.

Thanks.
Alex

 

[vongrunt]

> I'm loading 100s of thousands of records weekly that have a filed as DATETIME (12/26/2005 11:26:00).

Value itself is datetime, but everything is stored as char/varchar... or column data type is datetime?

> I would like to separate this one filed into two.

What is exact reason why you would like to do that? Hourly reports or something?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Walid]

If you don't mind me asking, what is the importance of separating the two? In any event, you can build a trigger that will extract the date portion in a variable and the time portion in another and then insert the time in a time field (the date portion if that field will default to January 1, 1900) and update the date in the date filed (the time portion will default to midnight)



Walid Magd (MCP)

The primary challenge of every software development team is to engineer the illusion of simplicity in the face of essential complexity.
-Grady Booch

 

[cybercop23]

The field type is datetime.
I want to sepate them because of two things:
1) need to create an index on date and don't think I would like the time included in that index and,
2) The user will access this data via an ASP page that will ask for the start date and end date for what they are looking for.

If I leave the datetime as one field then I'll have an issue when searching. If I only want to look at one day, then I'll have to put code in the ASP page to make it "between '%searchdate%' 00:00:00 and '%searchdate%' 23:59:59'.

Alex

 

[SQLDenis]

why not something like this?

select * from table where field >= @date
and field < dateadd(d,1,@date)

just pass in 1 date to the stored proc and put code like this in the proc

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

1) Storage space used for

a) datetime holding only date and
b) datetime holding date and time

... is identical - 8 bytes. And since b) case will likely produce more distinct values per index, no real benefits with two columns instead.

2) User types in date A and you want all records from that date:

WHERE blah >= @searchdate and blah < @searchdate + 1

Still simple and scales well (fully covered by eventual index).

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)