Changing date format. 1

[bj1]

Hi all

I have a quick question...

Currently the date format is:
'2003-01-28'

I would like it to be displayed in this format:
'28-01-2003' or '28/01/2003'

The date field is varchar, so does this mean that i have to convert it to datetime first?

Thank you for your help...

Cheers,
B

 

[SemperFiDownUnda]

look at CONVERT in books online
but quickly it would look like this

CONVERT(VARCHAR(25), GETDATE(),101)

the 101 USA mm/dd/yy format with a 4 digit year

look up CONVERT it has a table with all the ways to change the format.

 

[osjohnm]

Hi

As SemperFiDownUnda pointed out you must use a CONVERT function but you need to change his query to one of the following:

SELECT CONVERT(VARCHAR(25), GETDATE(),103)
RESULT: 15/04/2003

or

SELECT CONVERT(VARCHAR(@%), GETDATE(), 105)
RESULT: 15-04-2003

Just replace the GETDATE() function with your column(s).

John

 

[SemperFiDownUnda]

DOH! I gave you the Stupid American date format....before anyone gets upset the DATE format is stupid not Americans...I'm American just think mm/dd/yyyy is STUPID

 

[bj1]

i am using:

CONVERT(DATETIME, CONVERT(VARCHAR(10), recvddate, 105)) AS Date

and i get this:

2003-01-01 00:00:00.000

so its not actually changing the format of the date only adding the time on the end.

 

[osjohnm]

Hi bj1

The reason that the time is being added is that you are converting the varchar field to varchar first which does nothing to the actual recvddate.

Both SemperFiDownUnda and myself based our CONVERT on a datetime to varchar forgetting that your data is varchar so we steered you in the wrong direction.

Secondly you are converting recvddate to datetime so it adds the time at the end but has no affect on the field.

Rather swap the converts around like so:

convert(varchar(10), convert(datetime, recvddate), 105)

That will convert recvddate to datetime and then will aplly the varchar conversion making full use of the "105" option.

John

 

[bj1]

Thank you so much for your help - i was getting so confused!!!

Doing an excellent job - keep it up...

Cheers,
B