Adding varchar (as Hours:Minutes:Seconds) to a datetime field

Question

Adding varchar (as Hours:Minutes:Seconds) to a datetime field

Viewing 5 posts - 16 through 20 (of 20 total)

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

Lynn Pettis (9/23/2009)
Jeff Moden (9/22/2009)
As a side bar... this is why I cringe at the idea that 2k8 actually has separate datatypes for date only and time only. Everyone is going to start putting stuff like that into databases and end up with the same problems.
The only place I can really see the DATE and TIME data types being really useful is in a data warehouse. You can create a Date dimension and a Time dimension without having the play games with the dates.
Also, the DATE data type does make sense in those areas where time is not needed, such as DateHired, DateTerminated. Who cares what the time is there.

I tend to agree Lynn, there just aren't a lot of reasons to store time seperately.. I can think of a couple regarding a scheduling program that has regular business hours, but not a lot of other places..

CEWII

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

Generally speaking, it make no more sense to store date and time in separate columns that it does to store dollars and cents in separate columns.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

Michael Valentine Jones (9/24/2009)
Generally speaking, it make no more sense to store date and time in separate columns that it does to store dollars and cents in separate columns.

Again, I agree.. I also see even less of a reason given the scenario painted..

CEWII

amos-870870 SSC Veteran Points: 254 More actions · Answer 4

Given the information that is recorded it seems to me it would have been more usefull to store the datetime just as that datetime, not date with zero hours, and time. They did it the way they did because that is how you enter it, date and time seperate.

I am still deciding on how I want this other integrated app to function, then I am sure I will have more questions if I cant find the answers first.

Thanks Peoples

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 5

Lynn Pettis (9/23/2009)
Also, the DATE data type does make sense in those areas where time is not needed, such as DateHired, DateTerminated. Who cares what the time is there.

Heh... I do. Especially on the DateTerminated. Most of the companies I've worked for have marked the DateTerminated as the date and time a person actually leaves the building for the last time. Doing so saved one fellows hiney good an proper. Seems he looked remarkably like another fellow that pulled a felony on his day of termination... his ONLY alibi that was based on "evidence" instead of mere hear-say was the entry the HR rep made when the fellow left the building for the last time.

I'm with Micheal on this one... except maybe for PIVOTs in a data warehouse, storing a dates and times in separate columns is like storing dollars and cents in differnet columns. Same goes with storing dates with no times on original data... it just doesn't make sense to me especially since it costs nothing to keep them together.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)