Problem sa date between

Problem sa date between 18.01.2013. u 14:26 · pre 167 meseci #1

Napravio sam upit koji vraca ako neki column sadrzi parametar ili je null ali ne mogu da dodama i vreme.. tj. izmedju datuma

Code:

SELECT        ID, Title, keyword, jobtype, DateCreated, location
FROM            Test
WHERE        (@Title IS NULL) OR
                         (Title LIKE '%' + @Title + '%') AND (@keyword IS NULL) OR
                         (keyword LIKE '%' + @keyword + '%') AND (@jobtype IS NULL) OR
                         (jobtype LIKE '%' + @jobtype + '%') AND (@location IS NULL) OR
                         (location LIKE '%' + @location + '%') AND (DateCreated BETWEEN '1/10/2013' AND '1/12/2013')

Jel vidi neko sta je problem?

Potpis

To sto nekoliko miliona ljudi tvrdi da nisi u pravu ne znaci da stvarno nisi - Frank Zappa

https://youtu.be/DLe358DPGXU

Re: Problem sa date between 18.01.2013. u 14:37 · pre 167 meseci #2

Gotovo sam siguran da Datum mora biti u formatu "yyyy-mm-dd", a pritom nisam siguran da li moras da castujes DateCreated u date u slucaju da je tipa datetime.

Re: Problem sa date between 18.01.2013. u 14:47 · pre 167 meseci #3

Kad osttavim samo

Code:
DateCreated BETWEEN '1/10/2013' AND '1/12/2013'

radi OK...

Potpis

To sto nekoliko miliona ljudi tvrdi da nisi u pravu ne znaci da stvarno nisi - Frank Zappa

https://youtu.be/DLe358DPGXU

Re: Problem sa date between 18.01.2013. u 14:59 · pre 167 meseci #4

Jesi li siguran da ti ostatak upita vraca neki rezultat kada izbacis proveru datuma? Kako si originalno zakljucio da je datum problem?

Bez zagrada operator AND ima prednost i prvi ce se izvrsiti.

Re: Problem sa date between 18.01.2013. u 15:03 · pre 167 meseci #5

OD jutros u 5 sam poceo da se igram proverio sam sve i problem je da rade zajedno
Ovo radi OK

Code:
SELECT        ID, Title, keyword, jobtype, DateCreated, location
FROM            Test
WHERE        (@Title IS NULL) OR
                         (Title LIKE '%' + @Title + '%') AND (@keyword IS NULL) OR
                         (keyword LIKE '%' + @keyword + '%') AND (@jobtype IS NULL) OR
                         (jobtype LIKE '%' + @jobtype + '%') AND (@location IS NULL) OR
                         (location LIKE '%' + @location + '%')

Ovo radi OK

Code:
SELECT        ID, Title, keyword, jobtype, DateCreated, location
FROM            Test
WHERE        (DateCreated BETWEEN '1/10/2013' AND '1/12/2013')

Ovo ne radi OK, vraca sve

Code:
SELECT        ID, Title, keyword, jobtype, DateCreated, location
FROM            Test
WHERE        (@Title IS NULL) OR
                         (Title LIKE '%' + @Title + '%') AND (@keyword IS NULL) OR
                         (keyword LIKE '%' + @keyword + '%') AND (@jobtype IS NULL) OR
                         (jobtype LIKE '%' + @jobtype + '%') AND (@location IS NULL) OR
                         (location LIKE '%' + @location + '%') AND (DateCreated BETWEEN '1/10/2013' AND '1/12/2013')

Potpis

To sto nekoliko miliona ljudi tvrdi da nisi u pravu ne znaci da stvarno nisi - Frank Zappa

https://youtu.be/DLe358DPGXU

Re: Problem sa date between 18.01.2013. u 15:09 · pre 167 meseci #6

Udari zagradu tako da bude:

Code:

SELECT        ID, Title, keyword, jobtype, DateCreated, location
FROM            Test
WHERE        ((@Title IS NULL) OR
                         (Title LIKE '%' + @Title + '%') AND (@keyword IS NULL) OR
                         (keyword LIKE '%' + @keyword + '%') AND (@jobtype IS NULL) OR
                         (jobtype LIKE '%' + @jobtype + '%') AND (@location IS NULL) OR
                         (location LIKE '%' + @location + '%'))
                         AND (DateCreated BETWEEN '1/10/2013' AND '1/12/2013')

Re: Problem sa date between 18.01.2013. u 16:12 · pre 167 meseci #7

Ala si ih udarioooooo... Hvala, hvala, hvala, hvala ... Care, ako ikad dolazis u Nis rado bi ti pokazao jednu dobru kavanu...

Potpis

To sto nekoliko miliona ljudi tvrdi da nisi u pravu ne znaci da stvarno nisi - Frank Zappa

https://youtu.be/DLe358DPGXU

Re: Problem sa date between 19.01.2013. u 23:50 · pre 167 meseci #8

Ima nekoliko upita slicnih kao

Code:
(@Title IS NULL) OR (Title LIKE '%' + @Title + '%')

Imam osecaj da je problem u parametru, verovatno imas neku formu gde unosis te parametre, i tamo ako ne upises nista je prazan string sto je razlicito od NULL

Mozda je bolje da generises upit na osnovu unosa, prakticno u WHERE da budu samo oni koje si uneo.

Potpis

Pomozite Micro$oftu u borbi protiv piraterije, poklonite prijatelju Linux

Re: Problem sa date between 20.01.2013. u 00:18 · pre 167 meseci #9

Radi bas kako treba, a i kad sam ga pravio bas sam trazio nacin da ne pravim/generisem upit u codu....

Potpis

To sto nekoliko miliona ljudi tvrdi da nisi u pravu ne znaci da stvarno nisi - Frank Zappa

https://youtu.be/DLe358DPGXU

Forum kao aplikacija na telefonu

Srodne teme