Access like the other SQL database systems stores dates as a decimal number field with the integer part being the date and the decimal part being the time. The appearance of the date/time is done through formatting in each of the databases. For example, if you store only the time in a date field the system will default a date. I think in the case of Access the default is 12/31/1899 and in SQL Server it is 1/1/1900, it could be reversed. Access has several format funtions for date/time, such as, Format, FormatTime, and TimeSerial. The counterpart in SQL Server is the Convert funtion.
CONVERT (data_type[(length)], expression [, style])
The convert function is used to format any data type not just dates. There are many different styles, which you can use to display only the date, only the time, or any combination thereof.
Examples.
convert(varchar(12), @caseActivityDate)
convert(datetime,convert(int,CaseActivity.caseInvoicedDate))
convert(varchar(10),orderdate,101)
In sql server help there is documentation that shows all the possible styles that dates can be formatted into.