sql queries

[mo/-/sin]

hi.
i want to make a table in sql server 2005 and i want that there should
be two columns one if date and other for time......
i want that date column should show only date but not time and the
time column should show only time not date......
i tried this but when i enter only time it shows date also n that is
1900-01-01 and when i enter date it shows time also i.e 00:00:00...

[Erland Sommarskog]

Re: sql queries

mo/-/sin (Ri.mohsin@gmai l.com) writes:

i want to make a table in sql server 2005 and i want that there should
be two columns one if date and other for time......
i want that date column should show only date but not time and the
time column should show only time not date......
i tried this but when i enter only time it shows date also n that is
1900-01-01 and when i enter date it shows time also i.e 00:00:00...

On SQL 2005 there are no date-only or time-only data types. However,
SQL 2008, just released have this.

In SQL 2005, the best you can do is:

datecol datetime CHECK (datecol = convert(char(8) , datecol, 112)),
timecol datetime CHECK (convert(char(8 ), timecol, 112) = '19000101')

This constrains the time porttion for the date column to midnight, and
the date portion for the time column to 1900-01-01. The reason I picked
this date is that this is the "zero date" in SQL Server.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx