Working with DATE, TIME, TIMESTAMP in MS SQL!

Databases
1

Hi everyone!

I am working with a MSSQL 2000 database.

I am using PHP to display the table data. The table format (simplified) is as follows:

timekey, name, last_updated.

The timekey is in the following format:

2006031306595835900003

I believe this to be in the MSSQL TIMESTAMP format.

The last_updated column is in the following format:

2006-03-13 06:59:58.000

What I want to do is seperate the Time and Date from either one of these columns (at the database source) into seperate columns in their own right.

Thus allowing sorting of the table on Date or Time… i.e.

SELECT name, date, time FROM table ORDER BY date.

Anyone have any ideas? CAST or CONVERT? Im really stuck on this one…

Thanks in advance :slight_smile:

2

OK I have managed to do this using:

SELECT convert(varchar, last_updated, 103) AS date, convert(varchar, last_updated, 8) AS time FROM table

Is this the best way to do it? Correct? Should I be converting to VARCHAR?

3

that’s what you are doing

CONVERT(VARCHAR, last_updated, 103)

4

Sorry. I mean… is it correct to convert to VARCHAR? I have done some research and it seems there is no DATE only type in MSSQL. Only a DATETIME. If there was a date I could have converted it to DATE. This would allow me to use ORDER BY and get a correct date sort and not as is the case with VARCHAR an alphabetical search.

Thanks for your help!

5

if you have a DATETIME column, and you want it to be in date sequence, just sort it (using ORDER BY)

if you have a DATETIME column, and you want it to be in time sequence (e.g. if you want all times sorted together no matter which day), then ORDER BY CONVERT(VARCHAR,last_updated,8)

this works because style 8 is hh:mm:ss, i.e. from largest unit to smallest

6

Use DATETIME and have the front-end application handle the formatting.

TIMESTAMP in MSSQL has nothing to do with PHP/MySql/Unix timestamps. It is really for row versioning.