Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
alejandro
Starting Member
6 Posts |
 Posted - 2010-06-03 : 03:42:46
|
Hello all,I want to CREATE a new table but I am not sure about some specific data types, such as:* DATA : is this data-type correct? and I guess it expects YYYY-MM-DD as a value* TIME : is this also correct? (hh:mm:ss)* I have a field that can take just 2 values (2 specific strings). Is there any data type where I can declare these 2 strings in order to restrict from inserting ambiguous strings by using VARCHAR(x)Thanks a lot |
|
|
chris_cs
Posting Yak Master
223 Posts |
Posted - 2010-06-03 : 04:12:14
|
| I'm assuming by DATA you mean DATE?Date and Time values are combined in SQL Server as the DATETIME datatype. You could probably use a CHECK constraint to limit the column to contain two string parameters.Books online will provide you with all the information you need. |
 |
|
|
alejandro
Starting Member
6 Posts |
Posted - 2010-06-03 : 04:46:58
|
| Thanks you very much! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-03 : 05:03:02
|
" Date and Time values are combined in SQL Server as the DATETIME datatype"Although a DATE time (date only, no time part) was introduced in SQL 2008In addition to the CHECK constraint you could use a FOREIGN KEY to enforce that the values must exist in a second "lookup" table. Rather overkill for only two values  but would be worth considering if that number may grow in the future (for a CHECK CONSTRAINT you have to make a change to the database definition (DDL activity) which is a Programmer/Administrator activity, whereas for a FOREIGN KEY you need to add a suitable value to the Child table, which is something a user, with appropriate permissions etc., can do (DML activity) ) |
|
|
|
alejandro
Starting Member
6 Posts |
Posted - 2010-06-03 : 05:19:16
|
| Thanks Kristen. Yes actually that is what I read after some internet-search, that there is DATE and TIME data-types seperately. I would prefer to use them seperately than the DATETIME.Thank you for the FOREIGN KEY thing tip. I decided to use CHECK since it shouldn't grow in future. I used something like the above:CREATE TABLE myTable ( myDate DATE, myValue CHAR(6) CHECK (myValue in ("test-1","test-2"),...); |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-06-03 : 06:26:16
|
I thought we would use DATE a lot, although now we've got used to using DATETIME since ... <thinks!> about 1995 I suppose ... I've grown happy with it. We only need the DATE for an Invoice, but actually knowing the time when it was created comes in handy when diagnosing problems ... I think its mostly "old habits die hard" though  " I decided to use CHECK since it shouldn't grow in future."Yeah, makes sense.FWIW we always NAME such attributes - that makes it much easier to DROP them if necessary (otherwise SQL allocates a random name, which may vary from database-to-database if you run the script in different places). Probably not a problem if you only have one database ...So that would need to be something likeCONSTRAINT CK_myValue CHECK (myValue in ('test-1', 'test-2')) |
|
|
|
alejandro
Starting Member
6 Posts |
Posted - 2010-06-03 : 07:07:28
|
| Thanks you Kristen! Really very useful info. |
|
|
|
|
|
|
|
|
CREATE table - data types