Format Datetime type

[duongthaiha]

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

 

[AlexCuse]

SQL Server has lots of date formatting options you can use. Try this:

select convert(varchar(10), getdate(), 105)

for more:

http://sqljunkies.com/HowTo/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

Hope this helps,

Alex

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

Read this: thread183-1240616


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLDenis]

set datefrormat dmy will make sure you can insert it in ddmmyyyy however I recommend always using ISO format (112 or 120) which is YYYYMMDD

YYYYMMDD will always work no matter what language or coalation is used on SQL server

Lookup CONVERT in BOL to see how you can display it in your prefered way, however display logic should live at the presentation layer not inside the DB

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[bborissov]

SET DATEFORMAT DMY
But I always use so called ANSI standard YYYYMMDD when I insert date in datetime field:

Just an example

DECLARE @Test TABLE (Dt datetime)
DECLARE @cDate varchar(10)
SET @cDate = '20070910'
SET DATEFORMAT DMY
INSERT INTO @Test VALUES (@cDate)
SET DATEFORMAT MDY
INSERT INTO @Test VALUES (@cDate)

SELECT * FROM @Test

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLDenis]

>>But I always use so called ANSI standard YYYYMMDD

You mean ISO of course, it's okay it's Monday ;-)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[duongthaiha]

Thank you very much for your help.
There is problem with the database that I have to work with.
The default format of that table is in MM/DD/YYYY format. I don't want to change that as it will effect the rest of the system.

If I use the SET DATEFORMAT DMY command is that going to change all the table of the database? Or how long the effect of that command going to be? Is that gonna last until the the sql server is restarted?

Once again thank you very much

 

[bborissov]

Denis,
I always thought its ANSI, but you are right. Thank you.

duongthaiha,
SET DATEFORMAT wouldn't cjange HOW DateTime is stored in the table. No matter what is SET DATEFORMAT values in the fields are stored the same way. That settings affects only in representation of the DateTime and how SQL Server parse the string you pass to it to store it in the table.

As I and Denis said use that format YYYYMMDD when you want to store a string into DateTime field. That format wouldn't be affected of SET DATEFORMAT and always will be parsed right.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[gmmastros]

Did you read the thread I pointed out? thread183-1240616

[blue]>> The default format of that table is in MM/DD/YYYY format.[/blue]

This is not true. Dates are stored internally, without formatting. Whatever application you are using to view the data is presenting it in that format. Tables do NOT have a default date format. Logins have a default format.

[blue]>>If I use the SET DATEFORMAT DMY command is that going to change all the table of the database?[/blue]

Setting the date format will not affect the data in your tables. Since the date is stored without formatting, how could it?

[blue]>> Or how long the effect of that command going to be?[/blue]

As soon as you disconnect the session, the dateformat command will cease to have an affect.

[blue]>> Is that gonna last until the the sql server is restarted?[/blue]

No.

Let's do an experiment, shall we.

Open [!]2[/!] Query Analyzer windows. Now, understand that each window has it's own session. Using the SET DATEFORMAT command will only affect it's own session.

In one window copy/paste this....

Set DATEFORMAT MDY
Select Convert(DateTime, '4/6/2007')

Set DateFormat DMY
Select Convert(DateTime, '4/6/2007')

When you run this, the output will be:

[tt][blue]
-----------------------
2007-04-06 00:00:00.000

(1 row(s) affected)


-----------------------
2007-06-04 00:00:00.000

(1 row(s) affected)
[/blue][/tt]

In another window, run this...

Select Convert(DateTime, '4/6/2007')

Now, in the first window, run one of the set dateformat commands (MDY for example). Go back to the second window and re-run the command. Go back to the first window and run just the other DATEFORMAT command (DMY) go to the second window again and re-run the command.

The point of this experiment is that the SET DATEFORMAT command in one session has no affect on any other session.

As Denis and Borislav pointed out, using the ISO Unseparated Date Format (YYYYMMDD) will never be mis-interpreted by SQL Server. If you are using ADO to connect to the database, you could use the command object (with parameters) to prevent this problem.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLDenis]

Also read How are dates stored in SQl server

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[deSpec]

try this

select convert(varchar(10), getdate(), 103)


description from help for the last argument
100 default
101 USA
102 ANSI
103 British/French
104 German
105 Italian
..