Results 1 to 15 of 15

Thread: SQL Statement

  1. #1
    Join Date
    Dec 2004
    Posts
    7

    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'?

  2. #2
    Join Date
    Feb 2003
    Posts
    1,048
    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

  3. #3
    Join Date
    Jan 2005
    Posts
    2
    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.

  4. #4
    Join Date
    Dec 2004
    Posts
    7
    i have try this coding in "SQL Sever" but the result also not in this format, may i know why?

  5. #5
    Join Date
    Dec 2004
    Posts
    502
    If your data is 'MM/DD/YYYY', use this to convert to 'DD/MM/YYYY':

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

  6. #6
    Join Date
    Feb 2003
    Posts
    1,048
    datman & nosepicker,

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

  7. #7
    Join Date
    Dec 2004
    Posts
    7
    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.?

  8. #8
    Join Date
    Jan 2005
    Posts
    2

    Talking

    Hi Cash,

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

    Hope this is what you need.


    Sorry, Rawhide I am just trying to help!

  9. #9
    Join Date
    Dec 2004
    Posts
    7
    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

  10. #10
    Join Date
    Sep 2002
    Posts
    5,938
    You can list columns you like to see in select statement.

  11. #11
    Join Date
    Dec 2004
    Posts
    502
    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.

  12. #12
    Join Date
    Feb 2003
    Posts
    1,048
    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.

  13. #13
    Join Date
    Dec 2004
    Posts
    502
    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.

  14. #14
    Join Date
    Feb 2003
    Posts
    1,048
    Actually, I do see the differnece between what you said and what I said.

    I apologize. I was mistaken.

  15. #15
    Join Date
    Dec 2004
    Posts
    502
    Apology accepted. Thank you for being a stand-up person.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •