SQL Statement

may i know have this statement?

set date format 'dd/mm/yyyy'
select * from tablename

i have a data in my table and the date format in my table is mm/dd/yyyy, so when i run this sql statemet in query analyzer is it possible to list out the date format is 'dd/mm/yyyy'?

You're not using "Set DateFOrmat" correctly. The correct command would be:

Set DateFormat 'dmy'


However, it wouldn't do you any good to use it anyway. According to the BOL:

This setting is used only in the interpretation of character strings as they are converted to date values. It has no effect on the display of date values.

Here is what you want:

select Convert(varchar, DateFieldByName, 103)
from tablename

Hi cash/rawhide,

set dateformat 'dmy'

Is used tell SQL server that the input date is in certain format where it is referred to in the "SQL Query".

Example Query:

set dateformat 'dmy'
select *
from table_abc
where column_date = '11/12/2004'

-- The above query will interpret it as
Day = 11
Month = 12
Year = 2004

-- if dateformat is not set the query could have interpret it as
Day = 12
Month = 11
Year = 2004

Hope this make sense.

i have try this coding in "SQL Sever" but the result also not in this format, may i know why?

If your data is 'MM/DD/YYYY', use this to convert to 'DD/MM/YYYY':

SELECT CONVERT(varchar, CONVERT(datetime, DateColumn), 103)

datman & nosepicker,

You've both managed to merely repeat what I already said.

yes, i have used this script to run my program.

thank you for ur helpping..

last question that i have face from this script is how to ask the table show out the all table filed?

select convert(varchar, date, 103) as new_date from table name.

this script only show out the date and how about show out all table field.?

Hi Cash,

You can use:
select convert(varchar, your_date_column, 103) as new_date, * from table name

Hope this is what you need.
:cool:

Sorry, Rawhide I am just trying to help!
:D

Thank you, this will help me a lot of...
but if used this coding then will display 2 time of date.

anyway thank you for ur helpping

You can list columns you like to see in select statement.

Rawhide,

I did not repeat what you said. I was trying to point out that if your date starts out in 'MM/DD/YYYY' format, you cannot directly convert it to 'DD/MM/YYYY' format without converting it first to datetime format.

I said:

Here is what you want:

select Convert(varchar, DateFieldByName, 103)
from tablename


You said:

If your data is 'MM/DD/YYYY', use this to convert to 'DD/MM/YYYY':

SELECT CONVERT(varchar, CONVERT(datetime, DateColumn), 103)



You added nothing to the discussion.

Yes I did. Again, I was just trying to clarify what you said before and trying to make sure that if the data was starting in 'MM/DD/YYYY' format, that someone wasn't trying to convert it directly to 'DD/MM/YYYY' format by using "select Convert(varchar, DateFieldByName, 103)", which will not work. You need to convert DateFieldByName to datetime format first, then convert it to style 103.

You should tone down your arrogance if you want this to be an open discussion forum whose intention is to help people in a friendly manner. If you just want to shut people out, then you are denigrating the purpose of these forums.

Actually, I do see the differnece between what you said and what I said.

I apologize. I was mistaken.

Apology accepted. Thank you for being a stand-up person.