Validation - time format in varchar field

Question

Post reply

Validation - time format in varchar field

Anamika

SSCertifiable

Points: 6202
More actions
June 17, 2010 at 1:17 am

#223911

Hi All,
I want to capture the shift timings in a DB it'll be MM:SS.
what will be the correct way to capture it.
can i use DATETIME and update only the HH:MM columns or I have to go for a varchar with the format HH:MM? and if so how can i restrict the input and force the format hh:mm
Regards,
Ami

Viewing 7 posts - 1 through 6 (of 6 total)

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

Joel Ewald SSCertifiable Points: 6120 More actions · Answer 1

In SQL 2008 you could use the time datatype (12:35:29. 1234567)

Prior to that I would use Datetime.

I would use a datatype that understands date / time and not varchar.

There are many ways to then display / report the values HH:MM to the end user.

veeren4urs SSCrazy Points: 2763 More actions · Answer 2

Hi,

in sqlserver 2008 total 4 New date and time data types are added.

The previous versions of SQL Server had two date/time data types DATETIME and SMALLDATETIME. Both of them stored a DATE value and a TIME value together. SQL Server 2008 introduces four new date and time data types.

DATE

TIME

DATETIME2

DATETIMEOFFSET

Gothrough the below url you will have good idea on the data futures in SSMS 2008.

http://www.sql-programmers.com/Blog/tabid/153/EntryId/27/T-Sql-Programmability-Features-in-Sql-server-2008-Part-2.aspx

Veeren.

Thanks & Regards,
Veeren.
Ignore this if you feel i am Wrong. 😉

WayneS SSC Guru Points: 95460 More actions · Answer 3

Here's a quick example of the differences of the different datatypes. Note that to insert the time into the datatypes that support time, you have to prefix your MM:SS with "00:" to make it in HH:MM:SS format. If you are NOT running on SQL 2008, you'll have to remove the "[Time]" field from the table and inserts.

declare @test-2 table (ID int identity, [Time] time NULL, [DateTime] datetime NULL, [char] char(5) NULL, [smalldatetime] smalldatetime NULL)

declare @time char(5)

set @time = '12:55'

insert into @test-2 ([Time]) values ('00:' + @time)

insert into @test-2 ([DateTime]) values ('00:' + @time)

insert into @test-2 ([smalldatetime]) values ('00:' + @time)

insert into @test-2 ([char]) values (@time)

set @time = '12:63' -- invalid time!

begin try

insert into @test-2 ([Time]) values ('00:' + @time)

end try

begin catch

end catch

begin try

insert into @test-2 ([DateTime]) values ('00:' + @time)

end try

begin catch

end catch

begin try

insert into @test-2 ([smalldatetime]) values ('00:' + @time)

end try

begin catch

end catch

begin try

insert into @test-2 ([char]) values (@time)

end try

begin catch

end catch

-- show the results

select *,

RIGHT(convert(char(8), [Time], 8), 5),

RIGHT(convert(char(8), [DateTime], 8), 5),

RIGHT(convert(char(8), [smalldatetime], 8), 5)

from @test-2

Note that when the time is valid, all fields will enter the time. However, if for some reason the time is NOT valid, only the char field will accept it. Using this will allow invalid data, so don't use it.

Also note that since the precision of a smalldatetime is a minute, that the minutes were rounded up. So, don't use this either

The final select shows how to extract the data in MM:SS format.

So, in summary:

If you're on 2008, use the Time datatype, otherwise use the datetime datatype. Of course, the datetime datatype will also work on 2008.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

veeren4urs SSCrazy Points: 2763 More actions · Answer 4

Thats a very good inforamtion.Thanks a lot for the post Wayn.

Veeren.

Thanks & Regards,
Veeren.
Ignore this if you feel i am Wrong. 😉

WayneS SSC Guru Points: 95460 More actions · Answer 5

Glad it helps

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Anamika SSCertifiable Points: 6202 More actions · Answer 6

Thanks for the information.

I'm using sql server 2005. So I'm taking DATETIME data type. It further helps me to do some calculations on that column.

Regards

Ami