SQL in VB returning 30 Dec 1899

[WBH1138]

Hi
I'm running the following SQL Query in VB6...

SELECT
TimeValue([Time label]) AS [Time slot],
COUNT(TimeValue([Time label])) AS [Number of]
FROM Slot
WHERE
TimeValue([Time label]) BETWEEN #08:00:00# AND # 20:00:00#
AND [Time label] BETWEEN #03/01/2007 00:00:00# AND #03/07/2007 23:59:59#
GROUP BY TimeValue([Time label])
ORDER BY TimeValue([Time label])

The idea being that I get a count of the number of matching time slots on the table.

It works fine in Access but when running within VB6 the first field comes out as "30 Dec 1899"

Any idea why?

 

[gmmastros]

TimeValue is a function that returns the time portion of a Date variable. When Access displays a Date with the 'day' part = 0, it ONLY displays the time. VB does the same thing.

Ex:

[tt][blue]Debug.Print TimeValue(Now)[/blue][/tt]

Just now, when I ran it, it displayed (in the immediate window) [blue]10:45:04 AM[/blue].

However, eventhough it only displays the time in the immediate window, this does not mean there isn't a Date component to it.

Again, in the immediate window:

[tt][blue]Debug.Print TypeName(TimeValue(Now))[/blue][/tt]

This returns Date.

Additionally, to prove my point, in the immediate window:

[tt][blue]Debug.Print Format(TimeValue(Now), "yyyy-mm-dd hh:nn:ss AMPM")[/blue][/tt]

Output: 1899-12-30 10:50:10 AM


If you want to display ONLY the time part, then use this...

[tt][blue]Debug.Print Format(Now, "[!]hh:nn:ss AMPM[/!]")[/blue][/tt]

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Oh, I should also mention that there are some constants that you can use in the format function.

Ex:

Debug.Print Format(Now, "Short Time")
Debug.Print Format(Now, "Long Time")

These constants use the regional settings on the computer to determine the output. This is more important when you are displaying dates (because of internationalization issues).

Debug.Print Format(Now, "Short Date")
Debug.Print Format(Now, "Long Date")



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[WBH1138]

There are valid dates in the field as well as time and everything works OK in Access.

I'm thinking that as the SQL is built up within VB that the type of the data is being changed perhaps?

I will look at formatting it though, so thanks for that

 

[SBerthold]

>running within VB6 the first field comes out as "30 Dec 1899"

Just where does it come out?!

If you extract the value directly from the recordset it should only show a time as in
Debug.? rs.Fields("Time slot").Value
or
myStringVar= rs.Fields("Time slot").Value

If you are plugging this into a Date Variable, or into a bound control, then naturally it will show a date-time: You are adding the time to a Date, and the date portion has a default or start value of 30 Dec 1899 (numeric equivalent value of 0)

So, you need to stick it into a string variable, or format the bound control to show the date portion only, or do as gmmastros stated and format the Date variable when displaying.

 

[WBH1138]

Thanks all, using FORMAT worked, see below

SELECT
FORMAT(TIMEVALUE([Time label]),"Short Time") AS [Time slot],
COUNT(TIMEVALUE([Time label])) AS [Number of]
FROM Slot
WHERE
TIMEVALUE([Time label]) BETWEEN #08:00:00# AND #20:00:00#
AND [Time label]
GROUP BY TIMEVALUE([Time label])
ORDER BY TIMEVALUE([Time label])