How to change date format to french

Question

0.00/5 (No votes)

See more:

Hi All

i have a date format like 20150414 , and i want it to change it to 14 avril 2015 , so basically in french in stored procedure , anyone can tell me as i think there is no standard in ssms for it

any help would be much appriciated

Thanks

Posted 15-Apr-15 8:02am

saad88

Add a Solution

Comments

Maciej Los 15-Apr-15 14:49pm

What version of sql server?

Maciej Los 15-Apr-15 15:33pm

DECLARE @stringdate VARCHAR(30) = '20150414'
SELECT CONVERT(VARCHAR(30), CONVERT(DATE, @stringdate), 103) AS FrenchceDate

saad88 17-Apr-15 14:56pm

I am using SQL2005 , thats my problem , i have used this
update [dbo].[ss_MCB_master]
set expiry = CONVERT(VARCHAR(11), cast([Offer Expiry Date] as datetime), 106) AS [DD MON YYYY]
where Lang_code = 'F'
this works for select but not working on update , i know its giving me in english but i can make table and change it for french , because i have 2005 version , any help , and sorry basically its internal so i have to update database , please any help now

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2015-04-15T08:30:00

That's because it's a bad idea! :laugh:

Firstly, you shouldn't store dates as strings: you should always store them as DATE or DATETIME instead - and they don't have a format, they are stored as the number of microseconds since an arbitrary point in time. Storing dates as strings is both wasteful, and a PITA because it is very difficult to do useful comparisons on them.

Secondly, because formatting dates for display is not a database function - it really needs to be done in your presentation software instead, where it can present the data to the user in his preferred format. In the modern WWW world, it's silly to assume that the date format you use or prefer is going to suit all your users: even in a medium sized company in any European country you are going to get users who prefer to input and read dates in a different format!

So change your DB, store data in an appropriate format, and do conversions (from user input as early as possible, for output as late as possible) in your presentation software where you have access to the users preferred Cultural information.

Richard Deeming · Answer 2 · 2015-04-15T08:43:00

Solution 2

As Griff said, don't store dates as strings, and avoid formatting values in the database layer.

However, if you absolutely have to format a date in the database layer, and you're using Microsoft SQL Server 2012 or later, you can use the FORMAT function[^]:

SQL

DECLARE @D date = '20150415';
SELECT FORMAT(@D, 'dd MMMM yyyy', 'fr-FR');
-- Output: 15 avril 2015

Posted 15-Apr-15 8:43am

Richard Deeming

Comments

Maciej Los 15-Apr-15 14:49pm

Richard, as far as i remember, Format function is available in MS SQL server 2012 and higher. Please see: https://msdn.microsoft.com/en-us/library/hh213505.aspx

Richard Deeming 15-Apr-15 15:23pm

Which would be why I said, "if ... you're using Microsoft SQL Server 2012 or later". :)

Maciej Los 15-Apr-15 15:24pm

I'am blind, sorry...
:D

Maciej Los 15-Apr-15 15:26pm

+5!