Can't CAST as INT and back again

Question

Post reply

Can't CAST as INT and back again

dan-572483

SSCoach

Points: 15870
More actions
October 20, 2015 at 10:23 am

#305784

We have a table that stores a time in a char(5) format, as in '10:15' I want to advance the time by 15 minutes.
This is SQL2005, so I don't have a time datatype. I need to parse the char value and write the logic to advance to the new hour as needed:
declare @testTime char(5)
set @testTime = '10:15'
If right(@TestTime,2) = '45'
BEGIN
Set @testTime = CAST(CAST(Left(@testTime,2) as int) + 1 as char) + ':00'
If LEN(@testTime) < 5
SET @testTime = '0' + @testTime -- I expect hours <5 to not have the leading 0
END
Else
Set @testTime = Left(@testTime,3) + CAST(CAST(Right(@testTime,2) as int) + 15 as char)
select @testTime
Yes, I would also need logic to advance the hour to 00 from 23, but if we can't advance to hours < 24 properly it is a moot point.
If the minutes in the time value are 00, 15, or 30, the code works properly. But if the hour is advanced, the outer CAST back to char seems to be ignored as well as adding the ':00' and the select returns only hour.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

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

Try this:

set @testTime = '23:45';

select @testTime;

select @testTime = convert(varchar(5),dateadd(minute,(cast(left(@testTime,2) as int) * 60) + cast(right(@testTime,2) as int) + 15,cast('1900-01-01' as datetime)),108);

select @testTime

go

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 2

Or:

declare @testTime char(5);

set @testTime = '23:45';

SELECT @testTime = cast(dateadd(minute, 15, cast(@testTime as time) ) as char(5));

SELECT @testTime;

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

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

Alvin Ramard (10/20/2015)
Or:
set @testTime = '23:45';
SELECT @testTime = cast(dateadd(minute, 15, cast(@testTime as time) ) as char(5));
SELECT @testTime;

Remember, the OP is working with SQL Server 2005 so no time data type.

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

This problem deserves "KISS". 😀

--===== Setup the test. This is NOT a part of the solution.

DECLARE @testTime CHAR(5);

SELECT @testTime = '23:45';

--===== Exploit the simplicity of implicit conversions of the DATETIME datatype.

SELECT CONVERT(CHAR(5),DATEADD(mi,15,@testTime),108);

[EDIT] And it works in all versions of SQL Server back to at least SQL Server 7.

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

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 5

Lynn Pettis (10/20/2015)
Alvin Ramard (10/20/2015)
Or:
set @testTime = '23:45';
SELECT @testTime = cast(dateadd(minute, 15, cast(@testTime as time) ) as char(5));
SELECT @testTime;
Remember, the OP is working with SQL Server 2005 so no time data type.

Lynn, how can I remember something I had not noticed in the first place? 😉

Good point Lynn.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

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

Alvin Ramard (10/20/2015)
Or:
d eclare @testTime char(5);
set @testTime = '23:45';
SELECT @testTime = cast(dateadd(minute, 15, cast(@testTime as time) ) as char(5));
SELECT @testTime;

But with the above, the following will also work.

select @testTime = convert(varchar(5),dateadd(minute,15,convert(datetime,@testTime,108)),108);

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

Lynn Pettis (10/20/2015)
Alvin Ramard (10/20/2015)
Or:
d eclare @testTime char(5);
set @testTime = '23:45';
SELECT @testTime = cast(dateadd(minute, 15, cast(@testTime as time) ) as char(5));
SELECT @testTime;
But with the above, the following will also work.
select @testTime = convert(varchar(5),dateadd(minute,15,convert(datetime,@testTime,108)),108);

Still too complex. 😉 See my previous post.

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

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 8

Jeff Moden (10/20/2015)
This problem deserves "KISS". 😀
--===== Setup the test. This is NOT a part of the solution.
DECLARE @testTime CHAR(5);
SELECT @testTime = '23:45';
--===== Exploit the simplicity of implicit conversions of the DATETIME datatype.
SELECT CONVERT(CHAR(5),DATEADD(mi,15,@testTime),108);
[EDIT] And it works in all versions of SQL Server back to at least SQL Server 7.

Bad Jeff! Nobody said we could use the KISS principle! 🙂

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

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

Alvin Ramard (10/20/2015)
Lynn Pettis (10/20/2015)
Alvin Ramard (10/20/2015)
Or:
set @testTime = '23:45';
SELECT @testTime = cast(dateadd(minute, 15, cast(@testTime as time) ) as char(5));
SELECT @testTime;
Remember, the OP is working with SQL Server 2005 so no time data type.
Bad Jeff! Nobody said we could use the KISS principle! 🙂

😀

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

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 10

Jeff Moden (10/20/2015)
Alvin Ramard (10/20/2015)
Lynn Pettis (10/20/2015)
Alvin Ramard (10/20/2015)
Or:
set @testTime = '23:45';
SELECT @testTime = cast(dateadd(minute, 15, cast(@testTime as time) ) as char(5));
SELECT @testTime;
Remember, the OP is working with SQL Server 2005 so no time data type.
Bad Jeff! Nobody said we could use the KISS principle! 🙂
😀

Oops. Our quoting got out of sync.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

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

Jeff Moden (10/20/2015)
Lynn Pettis (10/20/2015)
Alvin Ramard (10/20/2015)
Or:
d eclare @testTime char(5);
set @testTime = '23:45';
SELECT @testTime = cast(dateadd(minute, 15, cast(@testTime as time) ) as char(5));
SELECT @testTime;
But with the above, the following will also work.
select @testTime = convert(varchar(5),dateadd(minute,15,convert(datetime,@testTime,108)),108);
Still too complex. 😉 See my previous post.

Maybe, but at least it explicitly shows what yours does implicitly.

Plus, looking at what Alvin posted made me realize there was an easier way, even if it explicitly does all the conversions.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 12

Is that time linked in any way to a date and if so, do you care about incrementing the data after midnight and do you care about Daylight Saving? (Adding 15 minutes takes you back 45 minutes or forward 1:15)

Do you have any CONSTRAINT in place to prevent bad data in that column that would/could cause a conversion to fail?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

dan-572483 SSCoach Points: 15870 More actions · Answer 13

Actually I have a server with about 50 databases (out of about 100 on the instance) I want change from daily backups to weekly backups within the backup solution we already have. When it is only one database there is a table for exceptions to the default backup policy where I would just insert a row with the name of the database and the backup schedule I want. I don't want to manually run 50 insert statements, so I'm writing a script to loop through the database names and add the necessary record for each affected database. I also don't want to launch 50 database backups at the same time, so I'm using a variable @BKTime, adding 12-15 minutes to it each time the loop runs to and writing the value into the char(5) StartTime column to stagger the backup start times.

It is interesting that I generally avoid implicit conversions because I understand that they can cause problems, but this is another example of how for everything you CAN do in SQL Server, there exists a scenario where it is appropriate to do it.