January 18, 2010 at 10:51 pm
I have the following case statement:
case pt.intPointTypeID
when 1 then convert(varchar(100), tet.dteTravelDate)
else '-'
end
I need the dtetraveldate as TravelDate in this case.But placing an As gives error,please help.
The travel date shall be returned '-' with the column name as Travel Date when the pointtypeid is not 1.How can we solve this?
January 18, 2010 at 11:18 pm
Hi,
What you have written in the statement is correct, but you should convert the date time type by convert (varchar (15), tet.dteTravelDate, 103)
(case when pt.intPointTypeID = 1 then convert (varchar (15), tet.dteTravelDate, 103)
else '-' end)as TravelDate
January 19, 2010 at 12:40 am
arun.sas (1/18/2010)
Hi,What you have written in the statement is correct, but you should convert the date time type by convert (varchar (15), tet.dteTravelDate, 103)
Not always, I guess it depends on what date format you want to display the data with. 103 is a uk/french format dd/mm/yy. By default it takes 100 i guess.
Create table #t(PID int, DTD datetime)
INSERT INTO #t VALUES (1, getdate())
INSERT INTO #t VALUES (2, getdate()-1)
INSERT INTO #t VALUES (3, getdate()-2)
SELECT PID,
CASE WHEN PID = 1 THEN CONVERT(Varchar(100),DTD) ELSE '-' END as TravelDate
FROM #t
---------------------------------------------------------------------------------
January 19, 2010 at 1:34 am
arun.sas (1/18/2010)
Hi,What you have written in the statement is correct, but you should convert the date time type by convert (varchar (15), tet.dteTravelDate, 103)
(case when pt.intPointTypeID = 1 then convert (varchar (15), tet.dteTravelDate, 103)else '-' end)as TravelDate
How do you know which format he wants to use? Maybe he wants to use a different format. Also maybe he wants to let the front end formatting the date, so different users will have different date format, but all of them will be able to use the same query.
As for the original question, most chances are that you didn’t specify the column alias in the correct place, but since you posted only small part of the code, we can’t know for sure. If the answers that you got by now didn’t help you, pleas post the question again, but this time show the entire query.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply