Adding Minutes(varchar) to a datetime field

Question

Adding Minutes(varchar) to a datetime field

Ells

SSCrazy Eights

Points: 9901
More actions
March 27, 2015 at 5:16 am

#302526

I have a datetime field that is just the date and zero for the time element. I also have a varchar field that is of the format 09:25:30
is there an easy way to add these together?
I don't mind if they get converted to integer as it will be used for comparison
Thanks
E

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

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

Ells (3/27/2015)
I have a datetime field that is just the date and zero for the time element. I also have a varchar field that is of the format 09:25:30
is there an easy way to add these together?
I don't mind if they get converted to integer as it will be used for comparison
Thanks
E

Quick solution converting the varchar to time and then add it to the datedime

😎

USE tempdb;

GO

SET NOCOUNT ON;

GO

DECLARE @SAMPLE_DATA TABLE

(

SID_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED

,SID_DT DATETIME NOT NULL

,SID_CT VARCHAR(8) NOT NULL DEFAULT(CONVERT(VARCHAR(8),CONVERT(TIME(0),GETDATE(),0),127))

)

;

INSERT INTO @SAMPLE_DATA (SID_DT)

VALUES

('2015-03-10')

,('2015-03-11')

,('2015-03-12')

,('2015-03-13')

,('2015-03-14')

;

SELECT

SD.SID_ID

,SD.SID_DT

,SD.SID_CT

,DATEADD(SECOND,DATEDIFF(SECOND,0, CONVERT(TIME(2),SD.SID_CT,0)) ,SD.SID_DT) AS COM_DT_CT

FROM @SAMPLE_DATA SD;

Results

SID_ID SID_DT SID_CT COM_DT_CT

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

1 2015-03-10 00:00:00.000 11:39:13 2015-03-10 11:39:13.000

2 2015-03-11 00:00:00.000 11:39:13 2015-03-11 11:39:13.000

3 2015-03-12 00:00:00.000 11:39:13 2015-03-12 11:39:13.000

4 2015-03-13 00:00:00.000 11:39:13 2015-03-13 11:39:13.000

5 2015-03-14 00:00:00.000 11:39:13 2015-03-14 11:39:13.000

djj SSCoach Points: 18831 More actions · Answer 2

I like Eirikur Eiriksson's solution.

A little more information can be found, http://stackoverflow.com/questions/7289753/combining-date-and-time-fields-to-datetime-sql-server-2008

ScottPletcher SSC Guru Points: 101097 More actions · Answer 3

Just add them and SQL will implicitly convert the varchar to a datetime anyway:

SELECT datetime_column + time_varchar AS new_datetime, ...

FROM ...

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 4

ScottPletcher (3/27/2015)
Just add them and SQL will implicitly convert the varchar to a datetime anyway:
SELECT datetime_column + time_varchar AS new_datetime, ...
FROM ...

Until the locale driven implicit conversion starts to produce 16:55 AM....;-)

😎

Ells SSCrazy Eights Points: 9901 More actions · Answer 5

I am impressed with Eirikur Eiriksson. That looks like a neat solution.

Thanks

E

ScottPletcher SSC Guru Points: 101097 More actions · Answer 6

Eirikur Eiriksson (3/27/2015)
ScottPletcher (3/27/2015)
Just add them and SQL will implicitly convert the varchar to a datetime anyway:
SELECT datetime_column + time_varchar AS new_datetime, ...
FROM ...
Until the locale driven implicit conversion starts to produce 16:55 AM....;-)
😎

Hmm, how could that happen? SQL will first convert the varchar to a datetime, then add the two, right!?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 7

ScottPletcher (3/27/2015)
Eirikur Eiriksson (3/27/2015)
ScottPletcher (3/27/2015)
Just add them and SQL will implicitly convert the varchar to a datetime anyway:
SELECT datetime_column + time_varchar AS new_datetime, ...
FROM ...
Until the locale driven implicit conversion starts to produce 16:55 AM....;-)
😎
Hmm, how could that happen? SQL will first convert the varchar to a datetime, then add the two, right!?

I agree with Scott. If the datatype is DATETIME, how will there be any "locale driven implicit conversion". Please post an example because, for now anyway, I have to say "Not possible" to run into such a problem.

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 8

Jeff Moden (3/27/2015)
ScottPletcher (3/27/2015)
Eirikur Eiriksson (3/27/2015)
ScottPletcher (3/27/2015)
Just add them and SQL will implicitly convert the varchar to a datetime anyway:
SELECT datetime_column + time_varchar AS new_datetime, ...
FROM ...
Until the locale driven implicit conversion starts to produce 16:55 AM....;-)
😎
Hmm, how could that happen? SQL will first convert the varchar to a datetime, then add the two, right!?
I agree with Scott. If the datatype is DATETIME, how will there be any "locale driven implicit conversion". Please post an example because, for now anyway, I have to say "Not possible" to run into such a problem.

Ooops, that came out slightly differently than I meant, the point I wanted to make was simply the hidden risk when relying on implicit conversions.

😎

One of the things I enforce is to adhere to BOL guidelines on International Transact-SQL by always using CONVERT with an explicit style parameter when working with date and time, in fact do I reject code that doesn't. Far too often have I had to deal with errors caused by this, sometimes very hard to spot or reproduce.

For fun, 8:04AM cuts of the seconds.

SELECT CONVERT(VARCHAR(8),CONVERT(TIME(0),'08:04:42',0),127) UNION ALL

SELECT CONVERT(VARCHAR(8),CONVERT(TIME(0),'08:04:42',0),0)

08:04:42

08:04:00

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 9

From your example, it would appear that this is more of a risk in explicit conversions than the implicit ones, in this case.

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 10

Jeff Moden (3/29/2015)
From your example, it would appear that this is more of a risk in explicit conversions than the implicit ones, in this case.

I know I'm not doing a good job of demonstrating Good/Bad examples here, should have kept some handy, having worked with/for companies with operations in almost all of the World's countrie, I've seen few;-)

😎

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 11

I've seen quite a few mistakes in temporal calculations. None of them were the direct result of adding a valid character based time to a DATETIME. I've seen it get the blame for human mistakes and human misunderstanding but I've never actually seen it to be the direct cause of a problem. I think that MS did us all a great disservice by removing the "direct" math capabilities on the other "newer" data-types. I'd have been quite happy if they had extended the "direct" math functionality to be more like EXCEL where you can add "times" (durations) in excess of 24 hours and have things work as expected.

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

djj SSCoach Points: 18831 More actions · Answer 12

Jeff Moden (3/29/2015)
I'd have been quite happy if they had extended the "direct" math functionality to be more like EXCEL where you can add "times" (durations) in excess of 24 hours and have things work as expected.

Silly boy, the Microsoft SQL team does not talk to the Microsoft Excel team. At least that has been my experience when trying to get help from them.:-D

ScottPletcher SSC Guru Points: 101097 More actions · Answer 13

I've also seen vast numbers of errors created by explicit conversions. If you're forced to deal with temporal data stored as char/varchar, then store it in a universal format only so that you never have to explicitly convert it: YYYYMMDD only, hh:mm:ss[.sss[ssss]] in 24-hr style only.

I also dislike the conversions codes because you have to look them up (at least I don't have them all memorized) to figure out exactly what's going on in the code.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Ed Wagner SSC Guru Points: 287026 More actions · Answer 14

Jeff Moden (3/29/2015)
I think that MS did us all a great disservice by removing the "direct" math capabilities on the other "newer" data-types. I'd have been quite happy if they had extended the "direct" math functionality to be more like EXCEL where you can add "times" (durations) in excess of 24 hours and have things work as expected.

+1 million. The datetime data type works great. It makes date math simple and it's uber-fast. It makes me wish I'd kept my performance comparisons I did on them, but alas, I did not. :angry: I'll stick with the old standby until the newer ones catch up the performance of the older one under the hood.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions