Help required on Dates over different cultures / locales

[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.

[techno]

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

onwards and upwards...

[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

[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

[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

[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.

[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]

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.

[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]

MS Access?

No. Non-relational databases with bitmapped indexes.