Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
joseph1975
Starting Member
6 Posts |
 Posted - 2012-02-24 : 09:08:24
|
| I'm tring to use this below in stored procedure, but the CONVERT(datetime,m.MtgDate,101) is not workingCASE WHEN m.MtgDate IN ('1/1/1900 12:00:00 AM')THEN 'TBD' ELSE CONVERT(datetime,m.MtgDate,101) END AS [ApprovedDate] how to modify the above statement to display TBD if the date is '1/1/1900 12:00:00 AM' and actual stored date if it is not '1/1/1900 12:00:00 AM'.Please help. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-02-24 : 09:45:50
|
If m.MtgDate is datetime (or smalldatetime or another datetime type), you should do CASE WHEN m.MtgDate = '19000101' THEN 'TBD' ELSE CONVERT(VARCHAR(10),m.MtgDate,101) END AS [ApprovedDate] But, that is converting the date to a string - which, most of the experts on this forum would advise you to do in your client application/presentation layer if you have one. |
 |
|
|
Jayam.cnu
Starting Member
45 Posts |
Posted - 2012-02-24 : 09:54:25
|
| can you try this ...case when convert(datetime,m.mtgdate,101)=convert(datetime,'1/1/1900 12:00:00 AM',101) then 'TED' |
|
|
|
Jayam.cnu
Starting Member
45 Posts |
Posted - 2012-02-24 : 09:55:06
|
| can you try this ...case when convert(datetime,m.mtgdate,101)=convert(datetime,'1/1/1900 12:00:00 AM',101) then 'TED' ELSE convert(datetime,m.mtgdate,101) end as [ApprovedDate] |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-24 : 10:13:19
|
quote:
Originally posted by joseph1975
I'm tring to use this below in stored procedure, but the CONVERT(datetime,m.MtgDate,101) is not workingCASE WHEN m.MtgDate IN ('1/1/1900 12:00:00 AM')THEN 'TBD' ELSE CONVERT(datetime,m.MtgDate,101) END AS [ApprovedDate] how to modify the above statement to display TBD if the date is '1/1/1900 12:00:00 AM' and actual stored date if it is not '1/1/1900 12:00:00 AM'.Please help.
The bottom line is you cant return multiple data type values using a CASE WHENit has to either date value in which case TBD doesnt make senseor it has to be varchar in which you need to convert all dates to varchar IMO this is not recommended to do at SQL end. You can very well return it as date only and do formatting at front end------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Jayam.cnu
Starting Member
45 Posts |
Posted - 2012-02-27 : 02:26:22
|
| yup... other wise case when convert(datetime,m.mtgdate,101)=convert(datetime,'1/1/1900 12:00:00 AM',101) then 'TED' ELSE convert(varchar,convert(datetime,m.mtgdate,101)) end as [ApprovedDate].... using this we can not get the Error... Does it make sence Visakh... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-27 : 20:30:24
|
quote:
Originally posted by Jayam.cnu
yup... other wise case when convert(datetime,m.mtgdate,101)=convert(datetime,'1/1/1900 12:00:00 AM',101) then 'TED' ELSE convert(varchar,convert(datetime,m.mtgdate,101)) end as [ApprovedDate].... using this we can not get the Error... Does it make sence Visakh...
it makes sense but is exactly what i meant by my second pointhere you're converting dates to varchar which can have issues if you're using value for some date manipulations afterwards like sorting,comparison etc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Date to string