phroureo phroureo - 5 months ago 9
SQL Question

SQL Server Datetime variables returning null values

I have the following code:

DECLARE @DispDateTime datetime

SELECT @DispDateTime = table1.U_dispDate + convert(datetime, right('0'+convert(varchar, table1.U_dispTime/100),2)+':'+right('0'+convert(varchar,table1.U_dispTime % 100 ),2),120)
from table1

select table1.callID,
@DispDateTime,
table1.U_dispDate + convert(datetime, right('0'+convert(varchar, table1.U_dispTime/100),2)+':'+right('0'+convert(varchar,table1.U_dispTime % 100 ),2),120)
from table1


You can see that the values in my select statement are exactly the same as what's in my variable declaration. However, my results end up looking like this:

callID DispDate Variable DispDate Calc
13 NULL 2016-04-04 07:07:00.000
15 NULL 2016-04-04 09:11:00.000
16 NULL 2016-04-04 07:10:00.000
19 NULL 2016-04-04 08:27:00.000
21 NULL 2016-04-04 07:39:00.000
23 NULL 2016-04-04 07:06:00.000
24 NULL 2016-04-04 10:20:00.000
25 NULL 2016-04-04 09:23:00.000


I can't for the life of me figure out why this is happening. I'm like 98% sure it's something simple and I'm actually an idiot, but it's the second time I've run into this issue in just a couple days. (It's also the first time, as far as memory serves, that I've had to use Datetime variables, so it could just as likely be something I don't know).

EDIT:

A) The reason that the times are stored as nvarchar is because that's what is dictated by the system (SAP Business One, if that matters). It's not something that I can change.

B) I think that what I really want to do is use a CTE, and that will fix my problem. I guess I just forgot how variables work or something (I.E. not set by row, but set once and then ran).

Thanks for the help everyone!

Answer

Maybe you have multiple results for your variable @DispDateTime.

You need a unique result for @DispDateTime.

For example:

SELECT TOP 1 @DispDateTime = table1.U_dispDate + ...

or a where clause.

Comments