Help required on Dates over different cultures / locales

[lilelvis2000]

Is there ever a question for which "MS Access" is the correct answer?

A lot of the work I do is in Access. Its a lot more capable than what people think. For one client...its the frontend to their budgeting system with over 3 million rows of data and some sophisticated financial calculations.

[techno]

Surely you would store the date in a DateTime type column which is independent of display format or locale.

Dunno if this helps...

http://www.sql-server-performance.co...e_2008_p1.aspx

http://www.blackwasp.co.uk/SQLDateTimeFormats.aspx

How important is the date? You probably do need to preserve the time zone in which case a DATETIMEOFFSET might do the trick. This might help along with bogeymans links:-

http://books.google.co.uk/books?id=H...um=3#PPA124,M1

http://blogs.msdn.com/bartd/archive/...-datetime.aspx

Thanks guys, will check them out...

[bogeyman]

Is there ever a question for which "MS Access" is the correct answer?

Don't knock it. It brought the power of the relational database and self-build GUI to millions of barely-techy office bods. Quite an achievement really.

Of course it was horrible under the covers, and made database purists wince, but it knocked any other end-user database tool into a cocked hat in terms of power and flexibility.

Quite an acheivement really.

Where is today's MS Access?

[MrMark]

Is there ever a question for which "MS Access" is the correct answer?

[bogeyman]

MS Access?

No. Non-relational databases with bitmapped indexes.

[blacjac]

So if that's a requirement, store elements as separate integers but at least maintain a proper datetime column alongside.

I've done quite a bit of data-mining work and agree when dealing with many millions of records, it's better to split the date into discrete integer columns - especially of you need to index them! If you're doing that kind of work then MS SQL probably isn't the tool of choice anyway.

MS Access?

[bogeyman]

Not so crazy. I've seen databases where days are given numeric values that look up separate records in a date_dimension lookup table. Normally on data mart schema. It's claimed that retrieval of data/calculations are quicker doing this rather than using date functions; I imagine it depends on the rdbms being used as to how big a difference this makes.

So if that's a requirement, store elements as separate integers but at least maintain a proper datetime column alongside.

I've done quite a bit of data-mining work and agree when dealing with many millions of records, it's better to split the date into discrete integer columns - especially of you need to index them! If you're doing that kind of work then MS SQL probably isn't the tool of choice anyway.

[MrMark]

Blimey EO, surely DateTime fields exist in databases so that you can store Dates/Times.

You can then do date arithmetic on them to calculate ages, policy renewal dates etc etc.

You can use built-in functions to format and parse them, according to locale.

What sort of lunatic would have "stored all the elements in seperate fields"?



You're having us on EO, I just know it.

Not so crazy. I've seen databases where days are given numeric values that look up separate records in a date_dimension lookup table. Normally on data mart schema. It's claimed that retrieval of data/calculations are quicker doing this rather than using date functions; I imagine it depends on the rdbms being used as to how big a difference this makes.

[bogeyman]

depends on the problem. In the past I have stored all the elements in seperate fields then concatenated them as and when

i.e. do it yourself rather than rely on any other fcker


good luck

Blimey EO, surely DateTime fields exist in databases so that you can store Dates/Times.

You can then do date arithmetic on them to calculate ages, policy renewal dates etc etc.

You can use built-in functions to format and parse them, according to locale.

What sort of lunatic would have "stored all the elements in seperate fields"?



You're having us on EO, I just know it.

[EternalOptimist]

depends on the problem. In the past I have stored all the elements in seperate fields then concatenated them as and when

i.e. do it yourself rather than rely on any other fcker


good luck

[ASB]

How important is the date? You probably do need to preserve the time zone in which case a DATETIMEOFFSET might do the trick. This might help along with bogeymans links:-

http://books.google.co.uk/books?id=H...um=3#PPA124,M1

http://blogs.msdn.com/bartd/archive/...-datetime.aspx

[bogeyman]

Surely you would store the date in a DateTime type column which is independent of display format or locale.

Dunno if this helps...

http://www.sql-server-performance.co...e_2008_p1.aspx

http://www.blackwasp.co.uk/SQLDateTimeFormats.aspx

[techno]

Hunting around looks like it is UTC format I need to store the date in.

onwards and upwards...

[techno]

Hi

I am trying to design a database (backend of a website) and just need a few pointers on the best way to handle dates, specifically storing them and then the display within webpages and reports.

I don't want people using this in USA confusing dates with people in UK.

I know this should be straight-forward but I would be grateful if anyone can give me a link to or explain the best practices for doing this.

Database: Microsoft SQL Server 2005.
Reports: Microsoft SQL Server Reporting Services
Webpages: .NET


TIA.