Professional Web Applications Themes

How to store Date in SQL Server? - Microsoft SQL / MS SQL Server

Hello, I have a problem with storing date in SQL Server, with locale setings not "United States" but others(E.g.Chinese - Taiwan). I created a function to change the date to this format: YYYY-MM-DD HH:MM:SS and it works(As before creating this function, there's a problem storing the date when the locale setting of my pc is "Chinese-Taiwan"). However, I was told that the locale settings of the Database can be change for e.g. 'Chinese - Taiwan' and 'United Status'. Hence, the storage of the date format in the database, will also be different. Is there a way that I can store ...

  1. #1

    Default How to store Date in SQL Server?

    Hello,
    I have a problem with storing date in SQL Server, with
    locale setings not "United States" but others(E.g.Chinese -
    Taiwan).

    I created a function to change the date to this format:
    YYYY-MM-DD HH:MM:SS and it works(As before creating this
    function, there's a problem storing the date when the
    locale setting of my pc is "Chinese-Taiwan"). However, I
    was told that the locale settings of the Database can be
    change for e.g. 'Chinese - Taiwan' and 'United Status'.
    Hence, the storage of the date format in the database,
    will also be different.

    Is there a way that I can store the date such that the
    locale settings of the Database will not affect the
    storing of the Date format.

    Thanks,
    Odelia
    Odelia Guest

  2. #2

    Default Re: How to store Date in SQL Server?

    See my other answer. Please don't repost (give it time).

    --
    Tibor Karaszi, SQL Server MVP
    Archive at: [url]http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver[/url]


    "Odelia" <peach82sg.sg> wrote in message
    news:0d9d01c345dc$30cfeb00$a101280aphx.gbl...
    > Hello,
    > I have a problem with storing date in SQL Server, with
    > locale setings not "United States" but others(E.g.Chinese -
    > Taiwan).
    >
    > I created a function to change the date to this format:
    > YYYY-MM-DD HH:MM:SS and it works(As before creating this
    > function, there's a problem storing the date when the
    > locale setting of my pc is "Chinese-Taiwan"). However, I
    > was told that the locale settings of the Database can be
    > change for e.g. 'Chinese - Taiwan' and 'United Status'.
    > Hence, the storage of the date format in the database,
    > will also be different.
    >
    > Is there a way that I can store the date such that the
    > locale settings of the Database will not affect the
    > storing of the Date format.
    >
    > Thanks,
    > Odelia

    Tibor Karaszi Guest

  3. #3

    Default Re: How to store Date in SQL Server?

    Odelia,

    SQL Server stores dates as a number and always stores them in the same
    format regardless of any locale settings. If you always use the ANSI
    international date format "YYYY-MM-DD HH:MM:SS" (as you have done here) to
    insert or update dates then SQL Server will always interpret the date
    correctly. The main problem comes when displaying dates using Microsoft
    tools such as VB or Enterprise Manager - here the date will be displayed
    according to your Windows regional settings. VB type conversion functions or
    implicit type conversions also use Windows settings to interpret dates and
    thus sometimes give programmers the impression that the dates are stored
    wrongly in SQL Server. (Programmers living in America probably never have
    this problem and wonder why the rest of us keep going on about date
    formatting.)

    I hope this helps.

    Pete

    "Odelia" <peach82sg.sg> wrote in message
    news:0d9d01c345dc$30cfeb00$a101280aphx.gbl...
    > Hello,
    > I have a problem with storing date in SQL Server, with
    > locale setings not "United States" but others(E.g.Chinese -
    > Taiwan).
    >
    > I created a function to change the date to this format:
    > YYYY-MM-DD HH:MM:SS and it works(As before creating this
    > function, there's a problem storing the date when the
    > locale setting of my pc is "Chinese-Taiwan"). However, I
    > was told that the locale settings of the Database can be
    > change for e.g. 'Chinese - Taiwan' and 'United Status'.
    > Hence, the storage of the date format in the database,
    > will also be different.
    >
    > Is there a way that I can store the date such that the
    > locale settings of the Database will not affect the
    > storing of the Date format.
    >
    > Thanks,
    > Odelia

    Pete Guest

  4. #4

    Default Re: How to store Date in SQL Server?

    Pete,
    > If you always use the ANSI
    > international date format "YYYY-MM-DD HH:MM:SS" (as you have done here) to
    > insert or update dates then SQL Server will always interpret the date
    > correctly.
    Unfortunately, that is not the case. As long as you use separators (dash, slash or dot) you are
    depending on the SET DATEFORMAT (etc) setting. It doesn't matter which separator you use:

    SET LANGUAGE french
    SELECT CAST('1998-05-23' AS datetime)
    GO
    SET LANGUAGE us_english
    SELECT CAST('1998-05-23' AS datetime)

    A safe format, however, is the unseparated format ('YYYYMMDD').
    --
    Tibor Karaszi, SQL Server MVP
    Archive at: [url]http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver[/url]


    "Pete" <pete_the_programmeryahoo.co.uk> wrote in message
    news:1057754046.852675ananke.eclipse.net.uk...
    > Odelia,
    >
    > SQL Server stores dates as a number and always stores them in the same
    > format regardless of any locale settings. If you always use the ANSI
    > international date format "YYYY-MM-DD HH:MM:SS" (as you have done here) to
    > insert or update dates then SQL Server will always interpret the date
    > correctly. The main problem comes when displaying dates using Microsoft
    > tools such as VB or Enterprise Manager - here the date will be displayed
    > according to your Windows regional settings. VB type conversion functions or
    > implicit type conversions also use Windows settings to interpret dates and
    > thus sometimes give programmers the impression that the dates are stored
    > wrongly in SQL Server. (Programmers living in America probably never have
    > this problem and wonder why the rest of us keep going on about date
    > formatting.)
    >
    > I hope this helps.
    >
    > Pete
    >
    > "Odelia" <peach82sg.sg> wrote in message
    > news:0d9d01c345dc$30cfeb00$a101280aphx.gbl...
    > > Hello,
    > > I have a problem with storing date in SQL Server, with
    > > locale setings not "United States" but others(E.g.Chinese -
    > > Taiwan).
    > >
    > > I created a function to change the date to this format:
    > > YYYY-MM-DD HH:MM:SS and it works(As before creating this
    > > function, there's a problem storing the date when the
    > > locale setting of my pc is "Chinese-Taiwan"). However, I
    > > was told that the locale settings of the Database can be
    > > change for e.g. 'Chinese - Taiwan' and 'United Status'.
    > > Hence, the storage of the date format in the database,
    > > will also be different.
    > >
    > > Is there a way that I can store the date such that the
    > > locale settings of the Database will not affect the
    > > storing of the Date format.
    > >
    > > Thanks,
    > > Odelia
    >
    >

    Tibor Karaszi Guest

  5. #5

    Smile Re: How to store Date in SQL Server?

    find seperate data value from database like YYYY,MM,DD,HH,mm,SS then devide as YYYY,MM,DD,HH,mm,SS same part and store depend by u

    Quote Originally Posted by Odelia View Post
    Hello,
    I have a problem with storing date in SQL Server, with
    locale setings not "United States" but others(E.g.Chinese -
    Taiwan).

    I created a function to change the date to this format:
    YYYY-MM-DD HH:MM:SS and it works(As before creating this
    function, there's a problem storing the date when the
    locale setting of my pc is "Chinese-Taiwan"). However, I
    was told that the locale settings of the Database can be
    change for e.g. 'Chinese - Taiwan' and 'United Status'.
    Hence, the storage of the date format in the database,
    will also be different.

    Is there a way that I can store the date such that the
    locale settings of the Database will not affect the
    storing of the Date format.

    Thanks,
    Odelia
    krishna India Guest

Similar Threads

  1. store/retrieve images to server
    By Suren_kancherla in forum Macromedia Flex General Discussion
    Replies: 1
    Last Post: April 11th, 07:02 AM
  2. date from server
    By Ike in forum PHP Development
    Replies: 2
    Last Post: June 26th, 07:32 AM
  3. Replies: 3
    Last Post: October 21st, 07:13 PM
  4. Server Date
    By Micheal in forum ASP.NET General
    Replies: 1
    Last Post: July 25th, 03:27 PM
  5. How to store Date in SQL Server
    By Odelia in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 9th, 06:26 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139