Date format with SQL backend

[Martix]

I am using access as the front end and have just ported the back end to an SQL system, Thje date/time format will not change from the long version that sql deafaults to, I cant work this time and date sensative database in this way... any suggestions please

 

[cmmrfrds]

Are you asking how to format a date/time field in a query? Give a little more detail or an example of what you want.

 

[Martix]

O.K>. the database is for a dispatch center in the private security industry. In regular access (.mdb) i can format the time date field to store in the tables as just time or just date or any combination thereof. Within my forms i was able to create or use existing input masks as well. After the port over now all of my date/time fields are extended versions. for example this is how the date of birth field looks in the employee information form and table now..(1977-12-28 00:00:00).i need it to simply read and be able to query on it looking like this 12/28/77 same for the short time field, does this make any more sense??

 

[cmmrfrds]

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.