locked
Format Datetime type in column RRS feed

  • Question

  • Hi
    Can you please help me on this matter please?

    I am trying to input UK(for example: 25/09/2007 ) format datetime in sql server. Is there any change that I can format my column to UK datetime. At the moment there is in US format(09/25/2007).

    I have a table, one of the column has type datetime.

    I use the insert command and the data i want to into is '25/09/2007'

    It come back with error that the date is out of range. I change that to 09/09/2007 then it work.
    The currently date and time format is MM/DD/YYYY.
    I don't know how can i define the format to be DD/MM/YYYY


    Thank you very much for your help
    i really do appreciate that.

    Best regards



    Monday, September 10, 2007 12:06 PM

Answers

  • Have a look at SET DATEFORMAT in Books Online. This sets the order of the dateparts in your date string.

     

    eg

     

    Code Snippet

    set dateformat dmy

    declare @datetime datetime

    set @datetime = '12/05/2007'

    select datepart(day, @datetime) as day, datepart(month, @datetime) as month

     

    set dateformat mdy

    declare @datetime datetime

    set @datetime = '12/05/2007'

    select datepart(day, @datetime) as day, datepart(month, @datetime) as month

     


    Note, its a good idea to use the ISO format for dates to remove any ambiguity ie 20070512.

     

    HTH!

    Monday, September 10, 2007 1:16 PM
  • Kenneth,

     

    Dates can be a little tricky to graspe sometimes due to the varied formats in which they come in. I think the problem is often that people believe what they put in is what they'll get out.

    Typically, the actual battle is "how to i get SQL Server to correctly understand my date input/output" and this is where there is a valid use for CONVERT, DATEFORMAT etc. Hence, I used 05/12/2007 specifically to illustrate the point of how DATEFORMAT can effect the output (Of course, its Eddie the Eagle Edwards birthday in Britain )

     

    You're right to point out the difference between storing and parsing dates and the effects of local/global settings and i agree its important to understand this.


    Cheers

    Wednesday, September 12, 2007 10:33 AM

All replies



  • Your question is not clear to me .Can you elaborate your question little bit more.
    thanx
    Monday, September 10, 2007 12:12 PM
  • Thanks a lot for your reply

    I have a table, one of the column has type datetime.

    I use the insert command and the data i want to into is '25/09/2007'

    It come back with error that the date is out of range. I change that to 09/09/2007 then it work.
    The currently date and time format is MM/DD/YYYY.
    I don't know how can i define the format to be DD/MM/YYYY

    Hope that make it clear.
    Once again thanks a lot
    Monday, September 10, 2007 1:10 PM
  • Have a look at SET DATEFORMAT in Books Online. This sets the order of the dateparts in your date string.

     

    eg

     

    Code Snippet

    set dateformat dmy

    declare @datetime datetime

    set @datetime = '12/05/2007'

    select datepart(day, @datetime) as day, datepart(month, @datetime) as month

     

    set dateformat mdy

    declare @datetime datetime

    set @datetime = '12/05/2007'

    select datepart(day, @datetime) as day, datepart(month, @datetime) as month

     


    Note, its a good idea to use the ISO format for dates to remove any ambiguity ie 20070512.

     

    HTH!

    Monday, September 10, 2007 1:16 PM
  • It's a common misunderstanding that you can insert and store a date into a datetime datatype in a certain format or style.
    You can't, so it's actually a waste of time trying to 'force' a specific format such as '12/05/2007' or '20071205' or any other variation.

     

    Whichever format you send to the server for storage, will not be stored in anyting that resembles a date to us humans.

    A datetime is stored as two integers internally, it doesn't look like a date at all.

    So, whatever date we send, it's not going to be stored in that format.

     

    What is important, though, is how a date is seen and parsed by the server. We want to be sure that there's no misunderstandings - that the server understands the format we send the date in to be the same as we intend.

    Just about all formats, such as '12/05/2007' or '05/12/2007' (btw - which date *is* that anyway? may or december?)
    are dependant on language and/or datesettings. This is pretty unsafe and opens up for unexpected conversions.

     

    The only 'safe format' is the one mentioned earlier in the thread, ssyymmdd - if you use that as input, you can be sure that nothing weird may happen that cause the date to change. However, it's still stored as two integers...

     

    To answer the question: if you need a special format when displaying a date, you don't do this at insert time, you do it when it's retrieved for display. In T-SQL you use CONVERT with the style parameter of your choice. If you don't use CONVERT and supply a style, then the format you'll see will be in the style that is the default for the tool that is used.

     

    /Kenneth

     

     

    Wednesday, September 12, 2007 8:52 AM
  • Kenneth,

     

    Dates can be a little tricky to graspe sometimes due to the varied formats in which they come in. I think the problem is often that people believe what they put in is what they'll get out.

    Typically, the actual battle is "how to i get SQL Server to correctly understand my date input/output" and this is where there is a valid use for CONVERT, DATEFORMAT etc. Hence, I used 05/12/2007 specifically to illustrate the point of how DATEFORMAT can effect the output (Of course, its Eddie the Eagle Edwards birthday in Britain )

     

    You're right to point out the difference between storing and parsing dates and the effects of local/global settings and i agree its important to understand this.


    Cheers

    Wednesday, September 12, 2007 10:33 AM