CODEWITHSUNDEEP
sql-server-2008asp-classic
Affan
Affan

Reputation: 359

change date format in asp

I cannot get the date format correctly in dynamic sql statement, after trying various ways of handling it. I need some help with the date format.

i am having a posting date field which is stored in sql db as yyyy-mm-dd format..and it displays in the asp web page as mm-dd-yyyy..i need to display the date in dd-mm-yyyy format..is there any way to do this?

this is my sql query:

SELECT CR.Name AS country,C.Name,C.[Name 2],C.[E-Mail],[Pre-Assigned No_], [Posting Date], SIH.[Currency Code], SIH.[Shipment Method Code],[Total Boxes],[ARE Net Wt],[ARE Gr_Wt], SIH.[Shipping Agent Code], SIH.[Payment Terms Code] FROM [Sales Invoice Header] SIH 
INNER JOIN [Country_Region] CR ON CR.Code = SIH.[Country of Final Destination] 
INNER JOIN [Customer] C ON C.No_ = SIH.[Sell-to Customer No_] WHERE SIH.No_ = 'PEXP1213-540'

Upvotes: 1

Views: 739

Answers (2)

Satinder singh
Satinder singh

Reputation: 10218

No need to replace direct use 106 or 105 and get the desired format
SELECT convert(varchar, getdate(), 106) – dd mon yyyy

so ur query look like

select  CR.Name as country,C.Name,C.[Name 2],C.[E-Mail],[Pre-Assigned No_], 
convert(varchar,[Posting Date], 106) AS [Posting Date], SIH.[Currency Code], SIH.[Shipment Method Code],
[Total Boxes],[ARE Net Wt],[ARE Gr_Wt], SIH.[Shipping Agent Code], SIH.[Payment Terms Code] from [Sales Invoice Header] SIH 
inner join [Country_Region] CR On CR.Code = SIH.[Country of Final Destination] 
inner join [Customer] C on C.No_ = SIH.[Sell-to Customer No_] where SIH.No_ = 'PEXP1213-540'

Also can do on codeBehind:

DateTime time = DateTime.Now;             // Use current time
string format = "dd mm yyyy";            // Use this format
Console.WriteLine(time.ToString(format));

Upvotes: 2

Naresh Pansuriya
Naresh Pansuriya

Reputation: 2045

please tried with this query

select  CR.Name as country,C.Name,C.[Name 2],C.[E-Mail],[Pre-Assigned No_], 
replace(convert(varchar(12),[Posting Date], 103), '/','-') AS [Posting Date], SIH.[Currency Code], SIH.[Shipment Method Code],
[Total Boxes],[ARE Net Wt],[ARE Gr_Wt], SIH.[Shipping Agent Code], SIH.[Payment Terms Code] from [Sales Invoice Header] SIH 
inner join [Country_Region] CR On CR.Code = SIH.[Country of Final Destination] 
inner join [Customer] C on C.No_ = SIH.[Sell-to Customer No_] where SIH.No_ = 'PEXP1213-540'

Note : Here [Posting Date] column is string, so need to just display.

Upvotes: 0

Related Questions