Date problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

Date problem

Author

Topic

ujjaval
Posting Yak Master

108 Posts

Posted - 2006-10-17 : 09:00:11

Hi I had my database in Sql Server 7.0. The database had date format of dd/mm/yyyy plus the timestamp. Now, I restored this database on Sql Server Express edition on my home pc and the date format changed to mm/dd/yyyy plus the timestamp. Does anyone know how can I reset this back to dd/mm/yyyy in Sql Express server?

Thanks,
Ujjaval

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-17 : 09:07:57

Change the REGIONAL SETTING in control panel.

Peter Larsson
Helsingborg, Sweden

ujjaval
Posting Yak Master

108 Posts

Posted - 2006-10-17 : 19:34:27

Well regional settings are not much of a help here. My computer setting is already for Australia and date format is dd/mm/yyyy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-18 : 00:56:43

Which collation do you use on the table?

Peter Larsson
Helsingborg, Sweden

ujjaval
Posting Yak Master

108 Posts

Posted - 2006-10-18 : 01:17:39

I don't know anything about collation :-(

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-18 : 01:27:07

Type following command in SQL QA to know current database collation:

Select DATABASEPROPERTYEX('db1','Collation')

Harsh Athalye
India.
"Nothing is Impossible"

ujjaval
Posting Yak Master

108 Posts

Posted - 2006-10-18 : 07:20:26

I got it. I am using following Collation:

SQL_Latin1_General_CP1_CI_AS on my database.

Is it the cause for storing dates in mm/dd/yyyy format?

What do I do that get these date in dd/mm/yyyy format?

Thanks,
Ujjaval

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-18 : 09:16:56

SET DATEFORMAT DMY

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-18 : 10:51:51

You dont need to worry how dates are stored in table
It is front end where you should format it the way you want

Madhivanan

Failing to plan is Planning to fail

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-10-18 : 18:09:10

To Harsh, BOL on SET DATEFORMAT:

quote:
This setting is used only in the interpretation of character strings as they are converted to date values. It does not affect the display of date values.

To Ujjaval, you never specified where you have the problem, but if it is in SSMS, then Peso is right, it is controled by the Regional setting of your Windows.

-- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don't know," Alice answered. "Then," said the cat, "it doesn't matter."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources