How to return a date in Australian date format?

Question

Post reply

How to return a date in Australian date format?

marin-231997

SSC Eights!

Points: 973
More actions
April 16, 2012 at 11:40 am

#392036

Hello all,
can somebody give me a hint on how to return a date in Australian date format?
I aware that presentation layer (i.e. application) should do this normally as there are many more possibilities to set locale and format correctly, but in this particular case I need it returned by SQL server: date will be part of concatenated string containing more information and that string as a whole is displayed in the presentation layer.
So basically I need a way to do sth. like this:
SET @dateString = CONVERT (datetime, '2012-04-05', 103)
SELECT CONVERT.....@dateString.......
result:
5/04/2012
...and the result '5/04/2012' would be processed further (i.e. concatenated with other string parts)
Many thanks for any hints...
Marin

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76985 More actions · Answer 1

Here are two different ways to do it.

DECLARE @dateString DATETIME

SET @dateString = '2012-04-05'

SELECT @dateString,

CASE WHEN DATEPART(DAY, @dateString) < 10

THEN STUFF(CONVERT(varchar(10), @dateString, 103), 1, 1, '')

ELSE CONVERT(varchar(10), @datestring, 103)

END

,RIGHT(CONVERT(varchar(10), @dateString, 103), 10-PATINDEX('0%', CONVERT(varchar(10), @dateString, 103)))

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Mike01 SSChampion Points: 11395 More actions · Answer 2

To avoid confusion for testing, use a day greater than 12.

Declare @DateString datetime

SET @dateString = CONVERT (datetime, '2012-15-04', 103)

SELECT CONVERT(nvarchar(20),@dateString,103) DateConverted

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

drew.allen SSC Guru Points: 76985 More actions · Answer 3

Mike01 (4/16/2012)
To avoid confusion for testing, use a day greater than 12.

Well, I think the point is that the format drops the leading zero on days, but not months, so using a day greater than 12 won't give you the condition that you are testing.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

mtassin SSC-Insane Points: 23099 More actions · Answer 4

Ummm?

SET DATEFORMAT DMY;

SELECT CONVERT(VARCHAR,GETDATE(),103)

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

marin-231997 SSC Eights! Points: 973 More actions · Answer 5

Hello Drew/Mike,

many thanks for the quick answers, much appreciated.

Drew is absolutely correct about my choice of sample date: this is because in AUS format zero is being dropped for day if <10.

Thanks again for the suggestions - I think I'll go for the second option from Drew.

Marin

drew.allen SSC Guru Points: 76985 More actions · Answer 6

mtassin (4/16/2012)
Ummm?
SET DATEFORMAT DMY;
SELECT CONVERT(VARCHAR,GETDATE(),103)

That format has a leading zero on both the day and the month (05/04/2012). The expected results given drops the leading zero on the day, but keeps it on the month (5/04/2012).

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

marin-231997 SSC Eights! Points: 973 More actions · Answer 7

...thanks also to Mark, will give it a try as well.

Marin

drooten Valued Member Points: 58 More actions · Answer 8

Mike01 (4/16/2012)
To avoid confusion for testing, use a day greater than 12.
[/code]

Excellent suggestion Mike01!

After all, testing with a date such as 01/01/2015 is useless if you want to be able to distinguish between 01 as a month and 01 as a day in the month.

In SQL 2005 this worked for me:

DECLARE @CurrentDate DATETIME

SET @CurrentDate = '2015-01-15'

SELECT CONVERT (char(10),@CurrentDATE , 103) as FullDateAU

--OUTPUT => 15/01/2015

HOWEVER, this line of code alone, did not

SELECT CONVERT (char(10), '2015-01-15' , 103) as FullDateAU

--OUTPUT => 2015-01-15

Even if I manually added the Time Component to the Var, it still did not work

SELECT CONVERT (char(10), '2015-01-15 00:00:00.000' , 103) as FullDateAU

--OUTPUT => 2015-01-15

drew.allen SSC Guru Points: 76985 More actions · Answer 9

drooten (12/3/2015)
Mike01 (4/16/2012)
To avoid confusion for testing, use a day greater than 12.
[/code]
Excellent suggestion Mike01!
After all, testing with a date such as 01/01/2015 is useless if you want to be able to distinguish between 01 as a month and 01 as a day in the month.
In SQL 2005 this worked for me:
DECLARE @CurrentDate DATETIME
SET @CurrentDate = '2015-01-15'
SELECT CONVERT (char(10),@CurrentDATE , 103) as FullDateAU
--OUTPUT => 15/01/2015
HOWEVER, this line of code alone, did not
SELECT CONVERT (char(10), '2015-01-15' , 103) as FullDateAU
--OUTPUT => 2015-01-15
Even if I manually added the Time Component to the Var, it still did not work
SELECT CONVERT (char(10), '2015-01-15 00:00:00.000' , 103) as FullDateAU
--OUTPUT => 2015-01-15

First, this thread is over three years old. You should be sure that you really have something to contribute if you want to resurrect an old thread.

Second, it was already discussed in this thread that picking a day greater than 12 excluded the conditions that were being tested--namely that leading zeroes are dropped when the day is less than 10, but retained for the month portion when the month is less than 10.

Third, it was already discussed that the format 103 would not work, because it retained the zero when the day was less than 10.

Fourth, your second and third examples have string inputs, not date/time inputs. The format string is ignored when converting strings to different strings.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA