Susan Geller
sgeller at cce.umn.edu
Thu Jul 15 13:28:16 CDT 2004
Actually, what I'm doing is trying to make data easier for users to work with. I have built an application with a very complex normalized data model which people are using. They want to be able to get at the data, do their own queries. They have LOW level Access skills. In general, a table join is above their skill level. So, I am writing a bunch of views that denormalize the data. The views are in SQL Server. I then have an Access database mdb file that links to those views. Users can create their own queries based on these views. I wanted to get rid of the timestamp on the date field values because that is not useful to them, but I want them to be able to sort by any of the many date fields that are in a single view. I didn't want them to have to use functions to display the dates correctly. I realize I could give them each date field twice -- once in a nice format and once to use for sorting, but the views already have so many fields in them that I hate to duplicate data just for this purpose. But, so far I haven't come up with an alternative. --Susan -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan Sent: Wednesday, July 14, 2004 5:08 PM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] Date field type On 14 Jul 2004 at 14:22, Susan Geller wrote: > In my sql server 2000 table, I have a date field. When I display that > field in a query it displays as yyyy-mm-dd and then a timestamp. I > want dd/mm/yyyy and no time. I converted it using this syntax > convert(varchar, milestone_dt, 101) and I get what I want, EXCEPT, > when I try to sort by that field, it sorts as a varchar, not as a > date, so I have all my January's together instead of all my 2004's > together. Can I have this format and still keep it as a date field so > it will sort correctly? > What sort of a query? If it's an Access query, draw the field as a date from the table and set the Format property of the field in the query design grid. If you don't need to sort dynamically, select the converted date but sort on the actual date field. -- Lexacorp Ltd http://www.lexacorp.com.pg Information Technology Consultancy, Software Development,System Support. _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com