Format Datetime type in column

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

Your question is not clear to me .Can you elaborate your question little bit more.
thanx|||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|||

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!

|||

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

|||

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