I am trying to prepare report on Livelink. I use following query.
problem in the query is that it display time in the date columne, ie.

Date1
------------------------
2004-09-08 00:00:00.000
2004-09-09 00:00:00.000
....

I want that this query should not display time, it only display date.

what change in the following query in the c.VALIDATE AS Date1 column ?


SELECT DISTINCT z.ID, a.VALSTR AS From1, b.VALSTR AS To1, c.VALDATE AS Date1, d.VALSTR AS Num
FROM dbo.ATTRDOCS_17183 z INNER JOIN
                      dbo.ATTRLINK_17183 a ON z.ID = a.ID INNER JOIN
                      dbo.ATTRLINK_17183 b ON z.ID = b.ID INNER JOIN
                      dbo.ATTRLINK_17183 c ON z.ID = c.ID INNER JOIN
                      dbo.ATTRLINK_17183 d ON z.ID = d.ID
WHERE (a.VALSTR =
                          (SELECT DISTINCT valstr
                            FROM AttrLink_17183 m
                            WHERE m.id = z.id AND m.RegionName = 'Attr_16336_15')) AND (b.VALSTR =
                          (SELECT DISTINCT valstr
                            FROM AttrLink_17183 m
                            WHERE m.id = z.id AND m.RegionName = 'Attr_16336_13')) AND (c.VALDATE =
                          (SELECT DISTINCT valdate
                            FROM AttrLink_17183 m
                            WHERE m.id = z.id AND m.RegionName = 'Attr_16336_8')) AND (d.VALSTR =
                          (SELECT DISTINCT valstr
                            FROM AttrLink_17183 m
                            WHERE m.id = z.id AND m.RegionName = 'Attr_16336_10'))


how can use cast clause in the above query with the alias that it should display only date not time ?

cast(datepart(day,appstrdate) as varchar(2))+' '+cast( datename(month,appstrdate) as varchar(3))+' '+cast( datepart(year,appstrdate) as varchar(4))

regards.

Mateen

try using convert

c.convert(char(10),VALIDATE,110) AS Date1 column


Jaime E. Maccou
Applications Analyst