format date in sqlserver

Posted by Klbaiju under Sql Server on 7/5/2013 | Points: 10 | Views : 1277 | Status : [Member] | Replies : 3
hi,

I want to format date in the following code

select Month_date, [100] ,[101],[102],[103]
from(
select t.Month_date,b.bus_id,b.tour_date,b.[status],b.fare from #temp t left outer join Busdetails b on t.Month_date=b.tour_date ) up
pivot (min(status) for bus_id in([100],[101],[102],[103]))
as pvt
order by Month_date
drop table #temp

I want to change this code
select t.Month_date,b.bus_id,b.tour_date,b.[status],b.fare from #temp t left outer join Busdetails b on t.Month_date=b.tour_date ) up
I want to change t.Month_date like
CAST(CONVERT(char(8), month_date, 112) AS datetime) date
I tried this but showing error.

Regards
Baiju




Responses

Posted by: Pandians on: 7/5/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
What is the DataType of "Month_date" column ?
(Or)
What is the value in "Month_date" column ?

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Adsingh_Naz on: 8/7/2013 [Member] Starter | Points: 25

Up
0
Down
SET DATEFORMAT DMY;
select CAST(CONVERT(char(8), t.Month_date, 112) AS datetime),b.bus_id,b.tour_date,b.[status],b.fare from #temp t left outer join Busdetails b on t.Month_date=b.tour_date ) up

Please try the above query you will be able to cast it to the expected format.

For more date format in sql server datetime, please visit the below URL

http://dotnetbites.com/date-format

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/8/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
It is very difficult to suggest correct solution with out knowing data type of Month_date column, sample data(in which format you are storing dates) and exact error message... Can you the error message?
The following link will give you the different date format conversions
http://www.sql-server-helper.com/sql-server-2008/sql-server-2008-date-format.aspx

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response