0

I have a excel where i read a dbf file with a select. I want to select the last datetime from each code_id.

Code_id daytime
1    12-01-2016 9:58:12
1    12-01-2016 10:01:12
2    12-01-2016 10:54:01
2    12-01-2016 11:01:01

A result i get:

1 12-01-2016 9:58:12
2 12-01-2016 11:01:01

I want result:

1 12-01-2016 10:01:12
2 12-01-2016 11:01:01

It seems there is a problem when the time go from 09 to 10, because if time go from 10 to 11 or 11 to 12 i get the last time, but when the time go from 9 to 10 it get the last time of 9 instead of time 10

Is there something wrong in my code?

SELECT b.code_id, COUNT(*) AS cnt, b.name, 
 b.type, a.tp 
FROM " & Filename & "  b 
INNER JOIN (select code_id, MAX(daytime) AS tp 
FROM " & Filename & " group by code_id) a 
ON  a.code_id = b.code_id 
where  DateValue(daytime) <= Date() 
group by b.code_id, b.name,  
b.type, a.tp 
order by b.code_id
Vasily
  • 5,707
  • 3
  • 19
  • 34
dave
  • 59
  • 1
  • 7
  • It seems to me that the datetime is not recognised as such but rather as a simple text. And within a text comparison 9 is after 1. That is 9:58:12 (compared as text) comes after 10:01:12. You need to convert it to datetime or add a 0 before the 9. – Ralph Jan 24 '16 at 01:16
  • How do i convert the field? – dave Jan 24 '16 at 08:31
  • You'll have to ask Excel to make the conversion. As you said already, the dbf can be only read as is. So, you'll first have to (1) import the entire data and then (2) reformat it so that the date and time is recognized and then you can (3) group it to you liking as shown in your above statement. To convert text to date and time you can use `CDate()` as shown in this example: http://stackoverflow.com/questions/20375233/excel-vba-convert-text-to-date – Ralph Jan 24 '16 at 09:41
  • Number 1,2 i get and i can do that, but how can i group it then? I have import the entire data, reformatthe the date and time, so i have a worksheet with all data and good datetime but what now? – dave Jan 24 '16 at 09:50
  • You can do the same as before. It will be something like this: `SELECT * FROM [Sheet1$]`. Check out these solutions and the multiple answers to them: http://stackoverflow.com/questions/18798522/run-sql-on-excel-table or http://stackoverflow.com/questions/8756802/excel-function-to-make-sql-like-queries-on-worksheet-data – Ralph Jan 24 '16 at 10:02

1 Answers1

0

From your description it seems that the daytime field is stored as text, not as a true datetime field. Since the time part is definitely not stored with leading 0s, such as 09, therefore the database compares the letter 9 with letter 1, and 9 is higher.

Possible solutions:

  1. Change daytime to be a true datetime field.
  2. Leave it as text, but make sure that both date and time components are stored with leading 0s.
  3. Convert daytime field in the sql to date.
Shadow
  • 33,525
  • 10
  • 51
  • 64
  • 1. Cannot change the daytime to true filed, the dbf is created by a other program. 2.also. 3. How to Covert the field? – dave Jan 24 '16 at 08:29
  • It depends on the driver you use to connect to the dbf file. With a search on the Internet it would be easy to find the function. – Shadow Jan 24 '16 at 09:49
  • Oledb driver for what? – Shadow Jan 24 '16 at 10:01
  • Most dbf drivers support the ctod() function that converts a string to date, so I'm a bit at loss to interpret your comment around "no working function". – Shadow Jan 24 '16 at 10:43
  • Oledb to connect to the dbf., how does the ctod() work? – dave Jan 24 '16 at 16:26
  • i cannot get it right, tried ctod(daytime, 01-01-01), – dave Jan 24 '16 at 20:06