Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2005
    Posts
    21

    Unanswered: Datetime data type ??

    I have a table in the database. One of the column is of datetime type.

    What is the best way to insert and retrieve value from that column. Here is the scenario

    1) I want to insert value '02/03/2006' into the column. How does the sql server know that the month is 02 and not 03. Will it look into the system settings. If so then can I sepcify custom format to distinguish between month,day and year.

    2) I want to retrieve value from the datetime column in the format dd/mm/yyyy hh:mm:ss AM/PM . What sql statement I have to use??



    Thanks

  2. #2
    Join Date
    Jun 2004
    Posts
    8
    I belive SQL uses the language setting to determine how you are entering the date, so when you are using the English language and Date is setup at 2/3/06 it assume mm/dd/yyyy. To retrun the date time value you want to return try using this:

    Select Convert(varchar(20),getdate(),109). To get the AM/PM setup try this website, http://sqladvice.com/blogs/repeatabl...2/22/4261.aspx
    They have a function already written to do that.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    1. SET DATEFORMAT

    2. The SQL Statement should only retrieve the data, not format it. The workstation client software ought to control the formatting. If you really want to cause yourself frustration, periodically reformat your boot drive... That will waste about as much time as formatting data on the SQL Server, and will be more obvious in how it wastes that time... If you prefer the subtle approach to wasting time, you can use the SQL Convert function to happily waste many hours!

    -PatP

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Pat Phelan
    The SQL Statement should only retrieve the data, not format it. The workstation client software ought to control the formatting. If you really want to cause yourself frustration, periodically reformat your boot drive... That will waste about as much time as formatting data on the SQL Server, and will be more obvious in how it wastes that time... If you prefer the subtle approach to wasting time, you can use the SQL Convert function to happily waste many hours!

    -PatP
    Had your coffee yet this morning, Mr. Sunshine?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman
    Had your coffee yet this morning, Mr. Sunshine?
    Oh, can't you just see me jazzed on caffine too? Egad!

    -PatP

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Web2000:

    In case it wasn't obvious from the silly description, I was trying to inject a bit of humor into the description of a very bad idea...

    While it is possible to format your data using SQL Server, I've never seen a case where it was a good idea. This actually makes it almost impossible to handle locale dependant issues, which in turn makes your code almost impossible to scale. If you handle the formatting at the client (even if that is a web browser), you can decide on a client-by-client basis how to handle the formatting (which is typically done for you with zero effort on your part), and you distribute the workload involved in that formatting. It is a win/win situation for you as a developer.

    -PatP

  7. #7
    Join Date
    Aug 2005
    Posts
    9
    One format that never fails is YYYY-MM-DD, no matter what are your regional settings, SQL Server always will correctly parse and uses a date formated in that way.

Posting Permissions

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