Hello

I have imported some mdb tables to SQL server 2005

Problem is that in access, you could specify date/time data type and use
format to display whatever you like: date or time, or both. But now, in SQL
server, data that was formatted as time is displayed as 30/12/1899 1:03:51
(only time is correct) and 14/02/2006 0:00:00 (only date is correct). And I
can’t find the format function switch in the SQL management studio, like I
could in access.

Why aren’t there smaller data types for only time and date separated? Is it
because they are all represented as 4 byte integers, so I could use smallint
instead and convert it to date/time when needed? If so, what’s the best
practice?

Is there any way to fix my problem so it displays those data correctly?

Thanks for help
Davor

You are confusing storage with presentation - which isn't surprising if you
re used to Access.

SQL Server will default to storing dates and times in datetime columns. The
fact that this will hold both a dat and time is immeterial - you don't have
to store both portions.
What is displayd is up to you - clients will probably default to displaying
both date and time but you can probably override that.

Your times should have a date of 19000101 as that is a 0 date in sql server
so you may have had a problem with your import.

Note that smalldatetime only holds minutes and datetime is not accurate to
the millisecond. Smalldatetime holds dates from 19000101 to 20790606 and
datetime from 17530101 to 99990101 so they may not be good for your data. If
you use other datatypes to store the data you may have problems with date
arithmetic.

To return just dates or times

convert(varchar(8),dte,112)
convert(varchar(8),dte,108)




"Davor" wrote:

> Hello
>
> I have imported some mdb tables to SQL server 2005
>
> Problem is that in access, you could specify date/time data type and use
> format to display whatever you like: date or time, or both. But now, in SQL
> server, data that was formatted as time is displayed as 30/12/1899 1:03:51
> (only time is correct) and 14/02/2006 0:00:00 (only date is correct). And I
> can’t find the format function switch in the SQL management studio, like I
> could in access.
>
> Why aren’t there smaller data types for only time and date separated? Is it
> because they are all represented as 4 byte integers, so I could use smallint
> instead and convert it to date/time when needed? If so, what’s the best
> practice?
>
> Is there any way to fix my problem so it displays those data correctly?
>
> Thanks for help
> Davor
>