CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Vlad
Vlad

Reputation: 2773

How to skip values that have NULL values in a TSQL query?

I have this rather complex query:

SELECT distinct 
    v.KodQ, v.DodPeriodika, 
    v.GodGP, v.Periodika as Period, 
    k.Oblast, k.KratokNazivSI, 
    k.NazivSI, k.Periodika, 
    v.rKod, r.Naziv, 
    v.rDatum,
    v.IT, v.Primerok, v.BrojIE, 
    CONVERT(varchar, CONVERT(DATETIME, v.RefPeriodOd), 103) as RefPeriodOd,
    CONVERT(varchar, CONVERT(DATETIME, v.RefPeriodDo), 103) as RefPeriodDo


    FROM GP.dbo.MyTable1 AS v 

    INNER JOIN GP.dbo.MyTable2 as k 
    ON k.KodSI = v.KodQ AND k.DodObr = v.DodPeriodika

    INNER JOIN GP.dbo.MyTable3 AS r 
    ON r.rKod = v.rKod

    WHERE v.GodGP = GodGP and CHARINDEX('-', RefPeriodDo, 0) != 0

I need to convert v.RefPeriodOd and v.RefPeriodDo from this format '31-Dec-2017' to this format '31/12/2017'.

Yes I know saving DateTime as string(varchar) is bad idea, but the application is now in use so I cannot really do QA at the moment and refactor my table.

I am using this statement:

CONVERT(varchar, CONVERT(DATETIME, v.RefPeriodDo), 103) as RefPeriodDo

But the problem is, v.RefPeriodOd and v.RefPeriodDo can have a NULL values and also values in the format: '31/12/2017'

If I remove this condition:

CHARINDEX('-', RefPeriodDo, 0) != 0

I get this error:

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

How can I add some conditions in the query?

Does IF statement exists in TSQL?

Upvotes: 3

Views: 829

Answers (5)

paparazzo
paparazzo

Reputation: 45096

Give this as try
Note the double use of 103 

declare @D table (id int identity primary key, dt varchar(20));
insert into @D (dt) values (null), ('messy'), ('31-Dec-2017'), ('31/12/2017'), ('1/12/2017');
select d.dt 
     , TRY_CONVERT(DATETIME, d.dt, 103) as dt103 
     , isnull(TRY_CONVERT(varchar(20), TRY_CONVERT(DATETIME, d.dt, 103), 103), d.dt) dt103var
from @D d;

dt                   dt103                   dt103var
-------------------- ----------------------- --------------------
NULL                 NULL                    NULL
messy                NULL                    messy
31-Dec-2017          2017-12-31 00:00:00.000 31/12/2017
31/12/2017           2017-12-31 00:00:00.000 31/12/2017
1/12/2017            2017-12-01 00:00:00.000 01/12/2017

Upvotes: 1

EzLo
EzLo

Reputation: 14189

Change

CONVERT(varchar, CONVERT(DATETIME, v.RefPeriodOd), 103) as RefPeriodOd

for

CASE 
    WHEN CHARINDEX('-', RefPeriodDo, 0) != 0 
    THEN CONVERT(varchar, CONVERT(DATETIME, v.RefPeriodOd), 103) 
END as RefPeriodOd

And remove the filter in the WHERE.

"IFs" are expressed with the CASE clause if inside SELECT statements, and the most common syntax is the following:

CASE
    WHEN 1stCondition THEN 1stValue
    WHEN 2ndCondition THEN 2ndValue
    ELSE DefaultValue -- Else is optional
END

Conditions are evaluated in order, and if no ELSE is issued, then NULL is returned.

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Note:

  • You cannot put the condition in the WHERE clause. The filtering is not necessarily before the expressions in the SELECT.
  • You should always include a length for strings in SQL Server.

The right way to handle this is try_convert():

TRY_CONVERT(varchar(255), TRY_CONVERT(DATETIME, v.RefPeriodOd), 103) as RefPeriodOd,
TRY_CONVERT(varchar(255), TRY_CONVERT(DATETIME, v.RefPeriodDo), 103) as RefPeriodDo

TRY_CONVERT() is available since SQL Server 2012. Before that, you needed to use a CASE to avoid conversion errors.

In my opinion, the filter condition should be:

RefPeriodDo LIKE '%-%'

I think this is easier to write and to read.

Upvotes: 3

DineshDB
DineshDB

Reputation: 6193

Try this:

SELECT distinct 
    v.KodQ, v.DodPeriodika, 
    v.GodGP, v.Periodika as Period, 
    k.Oblast, k.KratokNazivSI, 
    k.NazivSI, k.Periodika, 
    v.rKod, r.Naziv, 
    v.rDatum,
    v.IT, v.Primerok, v.BrojIE, 
    CONVERT(varchar, CONVERT(DATETIME, v.RefPeriodOd), 103) as RefPeriodOd,
    CONVERT(varchar, CONVERT(DATETIME, v.RefPeriodDo), 103) as RefPeriodDo
FROM GP.dbo.MyTable1 AS v 
INNER JOIN GP.dbo.MyTable2 as k 
ON k.KodSI = v.KodQ AND k.DodObr = v.DodPeriodika
INNER JOIN GP.dbo.MyTable3 AS r 
ON r.rKod = v.rKod
WHERE v.GodGP = GodGP AND ISNULL(v.RefPeriodOd,'')<>'' AND ISNULL(v.RefPeriodDo,'')<>''

Filter the rows using WHERE:

WHERE v.GodGP = GodGP AND ISNULL(v.RefPeriodOd,'')<>'' AND ISNULL(v.RefPeriodDo,'')<>''

Upvotes: 1

Csaba
Csaba

Reputation: 419

Try to add to the where the following:

WHERE (v.RefPeriodOd is not null and v.RefPeriodOd <> '')
  AND (v.RefPeriodDo is not null and v.RefPeriodDo <> '')

Upvotes: 3

Related Questions