Click here to Skip to main content
15,891,136 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
i have made a stored procedures in sql server 2008 i want to change the long date format into short date??

Please help need it urgently.. project is in process

What I have tried:

SQL
CREATE PROCEDURE [dbo].[OnelinePODate]
AS
BEGIN
	Select Main.Ibill_no,
       Left(Main.PoNo,Len(Main.PoNo)-1) As "PoNo",Left(Main.PoDt,Len(Main.PoDt)-1) As "PoDt"
From
    (
        Select distinct ST2.Ibill_no, 
            (
                Select ST1.PoNo + ',' AS [text()]
                From dbo.items_sold ST1
                Where ST1.Ibill_no = ST2.Ibill_no
                ORDER BY ST1.Ibill_no
                For XML PATH ('')
            ) [PoNo],
            (
                Select cast(ST1.podate as char(11)) + ',' AS [text()]
                From dbo.items_sold ST1
                Where ST1.Ibill_no = ST2.Ibill_no
                ORDER BY ST1.Ibill_no
                For XML PATH ('')
            ) [PoDt]
        From dbo.items_sold ST2
    ) [Main]
END
Posted
Updated 14-Sep-16 5:23am
v2
Comments
F-ES Sitecore 14-Sep-16 10:40am    
We don't have access to your data or your schema so we don't know what types your fields are. I'm going to assume that you are storing your dates as text, and that is your main problem...store dates as dates or else you end up having these issues. If you google how to convert varchar to a date I'm sure you'll find a solution.

The next thing that everyone who asks this question fails to understand is that dates don't have formats, they only gain a format when you convert them to a string so if you're looking to change the format of a date field in the database or in result set, you can't.
NIRMAL90 14-Sep-16 11:04am    
i have set the dates as data type smalldatetime

Date is date and nothing else! Due to the type of field[^] and several settings[^] date format may differ.

To display date using custom format, please check this:
Date and Time Formats[^]
CAST and CONVERT (Transact-SQL)[^]
FORMAT (Transact-SQL)[^]
 
Share this answer
 
Comments
Karthik_Mahalingam 14-Sep-16 13:05pm    
5
Maciej Los 14-Sep-16 13:37pm    
Thank you, Karthik.
It seems that you're trying to format date on the database server. I would advice not to do this. Instead fetch the date value to the client side as date and handle formatting over there. This way you can easily ensure that correct format is used.

In overall SQL is meant for data fetching and manipulation and it's a poor candidate for formatting. Other languages and tools are targeted for displaying data.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900