CODEWITHSUNDEEP
sql-servert-sql
Isaac Reefman
Isaac Reefman

Reputation: 597

Why does this variable not contain a value, where an identical SELECT statement does?

I'm writing a script that will name files in order to overwrite the appropriate one, and I need a 3-4 character hour readout to do it. So I wrote this:

DECLARE @Hour int = DATEPART(HOUR,Getdate());
DECLARE @ShortTime char(4) = IIF(@Hour < 13, STR(@Hour) + 'AM', STR(@Hour - 12) + 'PM');
SELECT @ShortTime;

Which gives me an empty string. However, the exact same thing in a select statement:

DECLARE @Hour int = DATEPART(HOUR,Getdate());
SELECT IIF(@Hour < 13, STR(@Hour) + 'AM', STR(@Hour - 12) + 'PM');

Works fine. Why is this? Can I use a variable here, or is it going to have to be big and unweildy?

Upvotes: 1

Views: 67

Answers (3)

Dale K
Dale K

Reputation: 27202

Very interesting observation! Which I wouldn't have expected.

The reason is because the STR function is returning a string of length 10 (the default), then you are adding 2 chars to it (total length 12) and then you are trying to assign it to a char(4).

If you look closely at the output of your select version you will see a lot of padding spaces before the value. The following works:

DECLARE @ShortTime char(4) = ltrim(IIF(@Hour < 13, STR(@Hour) + 'AM', STR(@Hour - 12) + 'PM'));

You could increase your string length if that is what you want.

DECLARE @ShortTime char(12) = IIF(@Hour < 13, STR(@Hour) + 'AM', STR(@Hour - 12) + 'PM');

The STR function does take a length parameter, but as you could get either a 1 or 2 digit result that probably won't be the solution for you.

Upvotes: 2

Ed Bangga
Ed Bangga

Reputation: 13006

This is because you are using datatype char(4) which is limited only for 4 characters.

Based on the result, it should be char(12)

DECLARE @Hour int = DATEPART(HOUR,Getdate());
DECLARE @ShortTime char(12) = IIF(@Hour < 13, STR(@Hour) + 'AM', STR(@Hour - 12) + 'PM');
SELECT @ShortTime;

Upvotes: 1

Avi
Avi

Reputation: 1845

The problem is not with variables but with the length of the string you are getting with concatenating.

As you mentioned below is giving you results but if you see the length it is 12. 

DECLARE @Hour int = DATEPART(HOUR,Getdate());
SELECT len( IIF(@Hour < 13, STR(@Hour) + 'AM', STR(@Hour - 12) + 'PM'));

As your @shorttime is just char (4) it will return blank as you have blank spaces (until 4th + characters).

You can check the blank spaces in this way. 

DECLARE @Hour int = DATEPART(HOUR,Getdate());
SELECT replace(IIF(@Hour < 13, STR(@Hour) + 'AM', STR(@Hour - 12) + 'PM'),' ', 'B') as Blank_check;

enter image description here

You can use convert instead of str. 

DECLARE @Hour int = DATEPART(HOUR,Getdate());
DECLARE @ShortTime char(4) = IIF(@Hour < 13, convert(varchar(10),@Hour) + 'AM', convert(varchar(10),@Hour - 12) + 'PM');
SELECT @ShortTime;

Upvotes: 2

Related Questions