Using the CAST or Convert functions

Question

Post reply

Using the CAST or Convert functions

patelxx

SSC Eights!

Points: 853
More actions
May 31, 2015 at 8:36 am

#308699

How do I use the CAST or CONVERT function in the code below, I require a third column (named Diff) which Minus the StartTime from the EndTime and the result is outputted in the third column (named Diff).
Calculation: @Diff = (@EndTime - @StartTime)
I still want the variables (@StartTime and @EndTime) to remain as nvarchar.
-------------------------------------------------------------------------------------------------------
The code:
DECLARE @StartTime nvarchar(10) = '12:10';
DECLARE @EndTime nvarchar(10) = '12:30';
DECLARE @Diff time(1) = '00:00';
SELECT @StartTime AS '@StartTime', @EndTime AS '@EndTimes', @Diff AS '@Diff';

Viewing 8 posts - 1 through 7 (of 7 total)

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

Phil Parkin SSC Guru Points: 246993 More actions · Answer 1

Maybe there's a better way, but this seems to work.

declare @StartTime nvarchar(10) = '12:10';

declare @EndTime nvarchar(10) = '12:30';

declare @Diff time(1) = cast(@EndTime as datetime) - cast(@StartTime as datetime);

select StartTime = @StartTime

,EndTime = @EndTime

,Diff = @Diff;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeff Moden SSC Guru Points: 1004435 More actions · Answer 2

Shifting gears a bit, wanting to keep these as NVARCHAR is...

1) a waste of space. Time is usually displayed only with certain characters and the double byte count of NVARCHAR plus the extra two bytes it takes to make it a variable width column is a blatant waste of space.

2) a waste of CPU time. As you just found out, any temporal calculations on these columns requires a conversion.

3) a waste of programmer time. Every time a programmer wants to do temporal calculations, they have to include conversion code and they have to get it right.

4) dangerous. What is preventing non-time values from being entered into the columns? Nothing.

The next part of the problem is the fact that these are start and end times with no date. What happens when a start time is just before midnight of one day and the end time is part of the next day? BOOM!

This is just wrong and I recommend that you get the powers that be to approve of a fix for it all.

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

Jeff Moden (5/31/2015)
The next part of the problem is the fact that these are start and end times with no date. What happens when a start time is just before midnight of one day and the end time is part of the next day? BOOM!

jeff...using Phil's code...what is wrong with this, assuming that it is not necessary to consider to days

declare @StartTime nvarchar(10) = '23:59';

declare @EndTime nvarchar(10) = '00:02';

declare @Diff time(1) = cast(@EndTime as datetime) - cast(@StartTime as datetime);

select StartTime = @StartTime

,EndTime = @EndTime

,Diff = @Diff;

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Jeff Moden SSC Guru Points: 1004435 More actions · Answer 4

J Livingston SQL (5/31/2015)
Jeff Moden (5/31/2015)
The next part of the problem is the fact that these are start and end times with no date. What happens when a start time is just before midnight of one day and the end time is part of the next day? BOOM!
jeff...using Phil's code...what is wrong with this, assuming that it is not necessary to consider to days
declare @StartTime nvarchar(10) = '23:59';
declare @EndTime nvarchar(10) = '00:02';
declare @Diff time(1) = cast(@EndTime as datetime) - cast(@StartTime as datetime);
select StartTime = @StartTime
,EndTime = @EndTime
,Diff = @Diff;

Apparently, nothing at all. :blush: My example was terrible.

However, is it possible that a start time could be at 9 AM on one day and the end time could be, say, 10:00 AM the next? In that case, it definitely won't give the correct duration.

declare @StartTime nvarchar(10) = '09:00'; --One day

declare @EndTime nvarchar(10) = '10:00'; --The next day

declare @Diff time(1) = cast(@EndTime as datetime) - cast(@StartTime as datetime);

select StartTime = @StartTime

,EndTime = @EndTime

,Diff = @Diff --Should be 25 hours, not 1.

I hate to rely on conditions based solely on esoteric requirements that could change and would do so without failure other than correctly producing the incorrect answer.

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

patelxx SSC Eights! Points: 853 More actions · Answer 5

patelxx

SSC Eights!

Points: 853

May 31, 2015 at 4:01 pm

#1801902

Thank you guys, you have been very helpful.

patelxx SSC Eights! Points: 853 More actions · Answer 6

Hi Phil,

What does the number 1 represent in brackets for the time data type - time(1)?

i.e. declare @Diff time(1)

DonlSimpson SSCertifiable Points: 7043 More actions · Answer 7

patelxx (6/1/2015)
Hi Phil,
What does the number 1 represent in brackets for the time data type - time(1)?
i.e. declare @Diff time(1)

That just defines the precision for fractions of a second. Default is "7" which is 100ns.

Don Simpson

I'm not sure about Heisenberg.