SQL Server & Dates

[mike55]

Hi all

 

I need to have two fields in two different tables, there is a requirment that one field just contains the date in the format dd/mm/yyyy, while the other field contains the time in the format hh:mm:ss.

 

Can anyone give some suggestions.

 

Mike55

[Nerseus]

First, to do it you're going to HAVE to store them as strings (varchar) since the requirements indicate storing them in a particular format.

 

Second, who made up this requirement? Unless it's a "snapshot" table that must store data in some exact format, this is usually the sign of a business analyst or manager that doesn't know what they're doing. As a point of reference, in my current system we have one database that has 500+ tables and only has one table that's a "snapshot" table. In the other half dozen databases used in the same project I don't think we have any snapshot tables.

 

-ner

[bri189a]

haven't heard that phrase before, I'm sure I know what it is but under a differant meaning...what do you mean by 'snapshot tables'

[Nerseus]

I don't know if it's a common term - just one we've used. A snapshot table is one that has pretty much NO foreign keys. It's more or less a reporting table that "flattens" out all the values.

 

For example, you might have a customer and an order table, linked by CustomerID. In the "snapshot" table you would store both table's data in one row.

 

We had to store the dates as varchar since the table was originally populated from converted data. The decision was made that even if the original data was bad, it was *exactly* what was printed and handed to a customer and, by law, had to be preserved. So if a date looks like "a/36/19998" then that's what we store. Obviously, a DateTime column wouldn't work here. It makes us all gumble to store things this way, but it's what we have to do - for this one table.

 

The real point is that this almost never happens. Since you can always control the date format for reports or other UIs, there's really no reason to store a date in a particular format in the database. Separation of the data from the presentation is key...

 

-ner

[bri189a]

Agreed, just curious about your definition...thanks!