Sql server date format - 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 2000 Forums

SQL Server Development (2000)

Sql server date format

Author

Topic

swatib
Posting Yak Master

173 Posts

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

In sql server date stored as MDY format. I want it as DMY.
I'm importing data from Ms Excel to sql server. The dates stored in Excel files are in DMY format. Its giving error while importing them in sql server.

is there any way to change the date format of the my database to DMY.

I refered the DataFormat commant but plz guide me in proper direction.

Urgent !!!!!!!11

Thanks

Njoy Life

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-03 : 01:20:50

The internal format of dates is decimal, so it DOES NOT MATTER which dateformat you store the dates. You should know this by now.

But, the point is that the dates stored as VARCHAR in the Excel sheet, might be different than the dateformat SQL Server uses.
If that is the case, use SET DATEFORMAT DMY, before importing the dates with OPENROWSET or similar function.

Peter Larsson
Helsingborg, Sweden

swatib
Posting Yak Master

173 Posts

Posted - 2006-10-03 : 03:13:04

It worked!!

But will it apply only to that selected database? other wise other database may hurt.......

Njoy Life

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-03 : 03:17:03

It will only apply to that session.

Peter Larsson
Helsingborg, Sweden

swatib
Posting Yak Master

173 Posts

Posted - 2006-10-03 : 03:41:07

session means? particluar selected database or for particluar tables?

I'm firing this query in QA.

Thanks a lot

Njoy Life

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-03 : 03:53:51

A session in QA is the current windows since you connected to the database.
When you close the current window, the session is also closed.
If you try to connect again, you loose all settings that were set in previous session. Hence, you have to run SET DATEFORMAT DMY again.

Peter Larsson
Helsingborg, Sweden

swatib
Posting Yak Master

173 Posts

Posted - 2006-10-03 : 04:08:01

Thanks for the info.

Could you plz tell me what are those settings command that expires after session is closed? Every SET command?

Njoy Life

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-03 : 04:12:10

I think the best place is to use GOOGLE or to read BOOKS ONLINE.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-03 : 08:57:06

quote:
Originally posted by swatib

In sql server date stored as MDY format. I want it as DMY.
I'm importing data from Ms Excel to sql server. The dates stored in Excel files are in DMY format. Its giving error while importing them in sql server.

is there any way to change the date format of the my database to DMY.

I refered the DataFormat commant but plz guide me in proper direction.

Urgent !!!!!!!11

Thanks

Njoy Life

Dont worry how Dates are stored in a table as long as you use proper DATETIME datatype. It is front end where you need to format the date in the format you want. Where do you want to show data?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources