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

Question

Post reply

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

amos-870870

SSC Veteran

Points: 254
More actions
September 22, 2009 at 10:46 am

#138995

I am interested in finding out how to make these two fields into one datetime, I will then be getting the time difference between DateTimeOn and DateTimeOff.
My data looks like this...
datetime varchar <--- Field Type
__________________________________________
DateOn TimeOn
2009-08-03 00:00:00.00012:00:00
[/code]
The time in the DateOn field is always 00:00:00.000. Any insight would be greatly appriciated.

Viewing 15 posts - 1 through 15 (of 20 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Here is 1 way:

DECLARE @time TABLE (theDate SMALLDATETIME, theTime VARCHAR(20));

INSERT INTO @time (

theDate,

theTime

) VALUES (

'2009-08-03 00:00:00.000',

'12:00:00' )

SELECT

theDate,

theTime,

CONVERT(SMALLDATETIME,(CONVERT(VARCHAR(20), theDate, 101) + ' ' + theTime)) theDateTime

FROM

@time

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

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

declare @time table (DT datetime , TM varchar(20))

insert into @time

select '2009-08-03 00:00:00.000', '12:00:00'

select DATE_TIME = DT+TM, * from @time

Results:

DATE_TIME DT TM

----------------------- ----------------------- --------------

2009-08-03 12:00:00.000 2009-08-03 00:00:00.000 12:00:00

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

Where did I go wrong, it is adding to the year, yet in your code it does not.

SELECT DateOn, TimeOn, TimeOff, DateOff,

CONVERT(SMALLDATETIME,(CONVERT(VARCHAR(8), DateOn, 101) + ' ' + TimeOn)) DateTimeOn,

CONVERT(SMALLDATETIME,(CONVERT(VARCHAR(8), DateOff, 101) + ' ' + TimeOff)) DateTimeOff

---Result---

DateOn TimeOn DateOff TimeOff DateTimeOn DateTimeOff

2009-08-03 00:00:00.000 12:00:002009-08-03 00:00:00.000 19:00:002020-08-03 12:00:002020-08-03 19:00:00

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

Never Mind I was foolish. varchar(20) is the answer.

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

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.

--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)

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

I have to agree Jeff, novice users with three datatypes and no understanding of why each of them is available. Recipe for more problems..

CEWII

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

Well in the case of my database, I understand why they separated it the way they do. It may have not been the best way, I think it was the easiest way for developing their front end application.

or am I not understanding your comment. Is this not a good way to make this happen?

Thanks for the assistance and insight.

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

I think the issues will become obvious when someone asks you to write a query returning data for a range of time like 2009-08-13 02:45:00 through 2009-10-15 06:13:00

select

*

from

MyTable

where

( MyDate between '2009-08-14' and '2009-10-15') or

( MyDate = '2009-08-12' and MyTime >= '02:45:00' ) or

( MyDate = '2009-08-15' and MyTime < '06:13:00' )

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

This is why I need to combine the 2 fields. Unfortunatly this is a small time, industry specific database / software package. They split the time the way they did I believe because it was the straight forward way to accomplish what they wanted to do with their front end application. Then they relied on crystal reports to convert / decipher the information in a useful way, such as time spent on a job.

Less effort would have been used if they had their front end combine the data entered, and write it to the database in one field.

So as far as my question here, which is how to work around the existing database structure to get what I want (time spent between the on and off time), is this a good way to go about it? This being the first step of combining the time information then the next step will be to get the difference between the 2 times. (I certainly don't want to make the same mistake the developers did, and do it because it is the easiest way for what I want right now.)

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

You could create a view that assembles the fields either Jack's way or Michaels's way..

CEWII

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

Elliott W (9/23/2009)
You could create a view that assembles the fields either Jack's way or Michaels's way..
CEWII

If the table is big, and you need good performance on date lookups, you might need to create an index on the view for the combined datetime.

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

Michael Valentine Jones (9/23/2009)
Elliott W (9/23/2009)
You could create a view that assembles the fields either Jack's way or Michaels's way..
CEWII
If the table is big, and you need good performance on date lookups, you might need to create an index on the view for the combined datetime.

True but that imposes a new set of problems for any queries unless they are using Enterprise Edition. You can get Standard to use those indexes but you have to give it hints othersie it will ignore them..

CEWII

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

amos-870870 (9/23/2009)They split the time the way they did I believe because it was the straight forward way to accomplish what they wanted to do with their front end application.

Heh... I know that you can't do anything about it now, but they should have left the date and time combined and created a view to do the split for the GUI. Ah well...

--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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

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.