Table Column with only time

Question

Table Column with only time

jkount 49745

Newbie

Points: 7
More actions
April 11, 2012 at 4:45 am

#256237

Hello all,
first of all my apologies if i opened this post in wrong section.
I need an advice for a problem i have with a database. Here is the problem:
I have a database in Sql2008 and i transfer it at SQL2005. Everything went fine except a problem i have with a table.
At that table, there are two (2) columns which the first gets the day (daytime Data Type) and the second gets the time (time(7) Data Type). At SQL 2005 there is no time(7) data type and i want to know how can i make that column to take only time.
a row as example is that: 2010-02-05 00:00:00.00011:13:36
Thank you for your time to read my post and hope to give me a solution 'cause something i tried didnt work.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 1

You down-graded from 2008 to 2005? You can't move the time column because they don't exist in 2005. You'll have to store it as a string, a varchar, or you'll have to combine the date and time into a datetime column. Those are really your only choices since 2005 doesn't have that data type. It was added in 2008.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

Why would you downgrade from 2008 to 2005?

I guess you could do a constraint. . .

CREATE TABLE test (ourTime CHAR(8));

ALTER TABLE test ADD CONSTRAINT chkTime CHECK(ourTime LIKE '[0-9][0-9]:[0-9][0-9]:[0-9][0-9]'

AND CAST(SUBSTRING(ourTime,1,2) AS INT) BETWEEN 0 AND 23

AND CAST(SUBSTRING(ourTime,4,2) AS INT) BETWEEN 0 AND 59

AND CAST(SUBSTRING(ourTime,7,2) AS INT) BETWEEN 0 AND 59);

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

jkount 49745 Newbie Points: 7 More actions · Answer 3

Problem Solved.

Ty all for fast replies.

Keep up the good Job

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

jkount 49745 (4/11/2012)
Problem Solved.
Ty all for fast replies.
Keep up the good Job

Forum etiquette would have you tell us how you solved the problem. Your solution may help others with a similar issue.