Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Location
    INDIA
    Posts
    9

    Unanswered: Set Dateformat Dmy

    HI FRIENDS,

    I AM FACING ONE DATE RELATED PROBLEM.

    MY Problem is that i want retrieve date from sql in dd/mm/yyyy format.
    i know that through convert fn , it is possible.
    but i want to retrieve date in specific format untill the connection is made with sql-server.

    I tried by using set dateformat dmy
    then i fired query
    like select getdate()
    but i am not geting required output

    hope u geting me.
    i urgely wait for yours reply.

    Thanking u
    dhiraj

  2. #2
    Join Date
    Oct 2003
    Posts
    9
    I am not sure whether this is what your looking for...

    SELECT convert(varchar, mydatefield, 103) as mydate FROM....

    HTH

  3. #3
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    Search the index for "convert" in books online, you will find all date-conversion data-formats there...notation is as flutedoodler just posted.
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  4. #4
    Join Date
    Jan 2004
    Location
    INDIA
    Posts
    9
    Thanks dear,
    i know that convert(char(10),getdate(),103) will return date in dd/mm/yyyy format.

    but my requirement is that without using that fn , can i get it by firing
    SET DATEFORMAT COMMAND.

    IN FOXPRO, THERE IS COMMAND LIKE SET DATE TO .........
    AND IN ORACLE ALSO SUCH COMMAND IS AVAILABLE.
    I AM NEWER TO SQL-SERVER.
    PLS IF U KNOW , HELP ME.

    AGAIN THANKS U
    DHIRAJ

  5. #5
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    SET DATEFORMAT applies to the input of a date, not the output.

    select CAST(DAY(dateValue) As VarChar(2)) + '/' +
    CAST(MONTH(dateValue) As VarChar(2)) + '/' +
    CAST(YEAR(dateValue) As Char(4))
    from table
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  6. #6
    Join Date
    Oct 2003
    Posts
    9
    Check out sp_defaultlanguage in Books Online, this enables you the change the language setting for logins which in turn will affect the formatting of your dates... Have a go see whether it solves your problem.

  7. #7
    Join Date
    Jan 2004
    Location
    INDIA
    Posts
    9
    Thanks for your kind attention

    You r right that "set dateformat is used for input not for output.
    Actualy i am working in VB as a frontend .
    I want to retrieve all date in dd/mm/yyyy without using conver fn each time when retrieve data from sql-server.
    i am not sure but i think there is way to retrieve date in specific format by seting some configation command. once such command is executed , date is retrieved in that format only for the current connection.

    My problem is that i do not know such configation command in sql server.

  8. #8
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    maybe you should be lookin at the problem in other angles
    errr...I don't know about VB, but in ASP, u need to specify how u want to present the date format from this command:
    "Session.LCID = 2057 "

    Could there be a VB command for application date display format?

    Else, this is 1 of the options I usually test out too...go to your regional settings of your computer and set the particular dateformat you want to display in your VB app.
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  9. #9
    Join Date
    Dec 2003
    Posts
    454
    What is the reason why you do not want to use the function for the output?

Posting Permissions

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