Problems with SQL CONVERT

[dazzer123]

I'm trying to run the stored procedure below

(
@StartDate varChar(10)

)

As
SELECT * FROM Messages WHERE EntryDate > CONVERT(smalldatetime,@StartDate,103) OR Remain = -1

/* set nocount on */
return

Yet whenever I run it I get all results where Remain = -1 regardless of EntryDate

I've never used CONVERT before so it's probably got something to do with that.

Any help will be greatly appriciated.

Regards
Dazzer

 

[Janoh]

try using cast, you use a style and don't know how is the impact if your locale is set on a different one.

Let the server handle the style, use it only when displaying.

CAST (@StartDate AS smalldatetime)

it's just a tought

 

[AtomicWedgie]

maybe you should replace the OR with AND?

 

[Janoh]

LOL, yes, you're right AtomicWedgie

it will take all where Remain = -1 regardless the date

 

[r937]

i would use ISDATE(@StartDate) and if that returns 1 (i.e. yes, it's a valid date) then just CAST(@StartDate as SMALLDATETIME)

CONVERT using the style values is for converting datetime columns to strings

rudy

http://r937.com/

SQL Consulting

 

[dazzer123]

Sorry I dont think I explained it very well, I want it to pick up all records where remain = -1 and also to pick all records where remain doesn't = -1 but EntryDate > @startDate.

Therefore OR is fine however the EntryDate > CONVERT(smalldatetime,@StartDate,103) is picking up all records with an EntryDate regardless of the value of @StartDate

EntryDate is smallDateTime and @StartDate is VarChar so this is why I am trying to do the CONVERT.

I will try some of the suggestions here and let you know what happens.

 

[AtomicWedgie]

WHERE Remain = -1
OR (EntryDate > CONVERT(smalldatetime,@StartDate,103) AND Remain <> -1)

Atomic Wedgie