Please help with datetime conversion 2

[developer77]

Hello everyone,

I have a stored procedure where I declare the start and end dates as datetime.

@StartDate datetime = null,
@EndDate datetime = null,
@SortColumnID Int = null

Then at the end of the stored procedure, I sorted it as follows:

ORDER BY
CASE @SortColumnID
WHEN 1 THEN p1.FirstName
WHEN 2 THEN p1.LastName
WHEN 3 THEN dt.StartDate
WHEN 4 THEN dt.EndDate
ELSE NULL
END,
p1.LastName,
p1.FirstName DESC

I get an error message saying that "Syntax error converting datetime from character string." when I plug in 1 or 2 for the @SortcolumnID. Can you help me convert the dt.StartDate and dt.EndDate to varchar like the others so that I don't get this error. I tried googling for the syntax to convert datetime to varchar but can't seem to find it. Thanks...

 

[SQLDenis]

example

select convert(varchar(30),getdate(),121)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[Catadmin]

The problem is, all the datatypes in a single CASE statement have to be the same. However, you can resolve your problem quite easily.

WHEN 3 THEN Convert(varchar(10),dt.StartDate,102)
WHEN 4 THEN Convert(varchar(10),dt.EndDate,102)

You'll want to look up Convert in Books Online to see what the 102 style code is and to see if you want to change it to another. Additionally, if you want to keep the time, you'll need to expand the Varchar from 10 to 23 (I believe that's the total characters).




Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[developer77]

Thank you both for your assistance.

 

[Catadmin]

You're welcome. @=)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"