[AccessD] SQL Time

"dd": I was referring to the t-sql (not Access) datepart-function, assuming this was a pass through query...
As to "distinct" you probably know that this is a costly operation which should be avoided if not strictly necessary. In this case, however, I don't think it hurts, because sql server hopefully won't perform the operation twice :)

Asger

-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] På vegne af Stuart McLachlan
Sendt: 12. december 2012 01:06
Til: Access Developers discussion and problem solving
Emne: Re: [AccessD] SQL Time

"dd" ?   Not a valid parameter to datepart() in Access.  

I'd stick with  CDATE(INT(...)) to get the date part of a datetime - or you could try  
DateValue(StatusDate).

You're right about the ....distinct....group by.....   I just do that out of habit :-)
-- 
Stuart

On 11 Dec 2012 at 23:32, Asger Blond wrote:

> I'm not able to test the query now, but I would suggest that you replace
> INT(StatusDate) with DATEPART(dd, StatusDate). Also the DISTINCT appears
> redundant to me since a GROUP BY is actually implying a distinct
> operation - but this is a minor point.
> Asger
> 
> ----- Original meddelelse -----
> 
> > Fra: "Kaup, Chester" <Chester_Kaup at kindermorgan.com>
> > Til: Access Developers discussion and problem solving
> > <accessd at databaseadvisors.com>
> > Dato: Tir, 11. dec 2012 23:13
> > Emne: Re: [AccessD] SQL Time
> > 
> > Other message got sent to soon please ignore
> > 
> > Tried this and got a select statement error
> > 
> > Select t1.* from dbo_DSS_StatusChanges t1 inner join (select distinct
> > PID, INT(StatusDate), max(StatusDate) as lastdatetime
> > group by PID, INT(StatusDate))
> > t2 on t2.PID = t1.PID AND t2.lastdatetime = t1.StatusDate;
> > 
> > I the modified the select statement and came up with this
> > 
> > Select t1.* from dbo_DSS_StatusChanges t1
> > inner join
> > (select distinct PID, INT(StatusDate), max(StatusDate) as
> > lastdatetime
> > from dbo_DSS_StatusChanges
> > group by PID, INT(StatusDate))
> > t2 on t2.PID = t1.PID AND t2.lastdatetime = t1.StatusDate;
> > 
> > I now get an error of [Microsoft][ODBC SQL Server Driver][SQL Server]
> > Disallowed implicit conversion from data type datetime to data type
> > float, table 'dbo.DSS_StatusChanges', column 'StatusDate'. Use the
> > CONVERT function to run this query. (#260)
> > 
> > 
> > Thanks for the help.
> > 
> > 
> > 
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart
> > McLachlan
> > Sent: Tuesday, December 11, 2012 3:30 PM
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] SQL Time
> > 
> > Something like:
> > 
> > Select t1.* from mytable t1
> > inner join
> > (select distinct ParentFK, INT(rDateTime), max(rDatetime) as
> > lasdatetime
> > group by ParentFK, INT(rDateTime)) t2 on t2.ParentFK = t1.ParentFK
> > AND t2.lastdatetime = t1.rdatetime
> > 
> > The inner Select should return the FK and the datetime of the last
> > record for each day for each Parent entity. Joining on those two
> > fields should give you just the records you want.
> > 
> > --
> > Stuart
> > 
> > On 11 Dec 2012 at 20:58, Kaup, Chester wrote:
> > 
> > > I have a database with linked SQL server table. I need to extract
> > some
> > > records from the table. Some days there is more than one record; ie
> > > two records with different times. I need to grab the last record on
> > > those days. So far I have not come up with a way. Your ideas
> > > appreciated. -- AccessD mailing list AccessD at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/accessd Website:
> > > http://www.databaseadvisors.com
> > >
> > 
> > 
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> > 
> > 
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com