Timestamp for the date only

[ckitzman]

I'm trying to extract data from a SQL table that has the date timestamp datatype.

so it looks like this: 2005-03-29 16:57:06.007

How can I query for a date range while ignoring the time part of the stamp.

EX. a parameter query for a month worth of encounters using the above date format.

thanks in advance.

 

[gemma-the-husky]

it is stored as a date -just put in the criteria

between #01/09/06# and #30/09/06#

Now
a) check the date format, it should be taken as UK or US date depending on your regional setting, but you never know

b) because the stored dateINCLUDES time, some postings here say this will criteria will not include dates of 30/09/06, so you may need to do

between #01/09/06# and #01/10/06# to pick up the postings for 30/9/06


Explicit Dates have to be put inside # symbols, although you can use variable references as normal.

 

[ckitzman]

Thanks. the date is stored in SQL just as it appears above. When I run a parameter query for encounters I KNOW are in there, I get nothing. I'm thinking that becasue I'm not including the time portion in the parameter.
Between [Enter Beginning Date of Report] And [Enter Ending Date for Report]

why won't the above parameter work? I'm being consistent witht the date format


Explicit Dates have to be put inside # symbols, although you can use variable references as normal.[/QUOTE]

 

[gemma-the-husky]

try it with an explicit date range, enclosed in hashes. See if that picks up some.

I would have thought there would be no problems, providing the sql is stored as a date/time type. I am sure the time is not the problem.

 

[boblarson]

You can try this:

Between Format([Enter Beginning Date of Report],"yyyy/mm/dd 00:00:00") And Format([Enter Ending Date for Report],"yyyy/mm/dd 11:59:59")

 

[raskew]

Hi -

Take a look at this MSKB article http://support.microsoft.com/kb/q130514/ to see how Access stores dates/times.
After reviewing the above, take a look at

2005-03-29 16:57:06.007

It absolutely doesn’t work. What’s wrong…the .007 suffix! Dump the .007 suffix, and you’ve got 2005-03-29 16:57:06.

Check out the DateValue() function.

Best wishes - Bob

 

[ckitzman]

Thanks.

Access isn't storing the data. I'm using Access to query a SQL database through linked tables I cannot make modifications to the SQL storage procedure.

--ck

 

[stopher]

I agree with raskew. The .007 is not good for a date in access. You don't need to physically chop off the end. Just use:

DateValue(LEFT([theDateStamp],19)) between #01/09/06# and #30/09/06#

hth
Stopher

 

[meloncolly]

folks, the problem is ckitzman is using SQL Server as the back end; SQL server does indeed store the dates just the way ckitzman says - the .007 is milliseconds.

I'd strip out the year, month and date and then re-combine into just a date in a query, then add your criteria (as describe by Gemma-the-husky) to this new field:

JustADate: dateserial(year([your date time field]),month([your date time field]),day([your date time field]))

 

[stopher]

folks, the problem is ckitzman is using SQL Server as the back end; SQL server does indeed store the dates just the way ckitzman says - the .007 is milliseconds.

I don't think anyone is questioning whether the .007 is valid in another dbms or not. The problem is whether Access is capable of interpreting it. Personally I don't think Access can (see Reskews link). Hence I doubt year(), month()and day() will work directly.
This....
Year(#29/03/2005 16:57:06#)
works
but this....
Year(#29/03/2005 16:57:06.007#)
throws a wobbly

However, I don't have sql server installed so I can't test either theory properly.

Stopher

 

[raskew]

Hi -

Just spent a few minutes in Google. There are numerous posts on this problem and the general consensus is that the Jet engine lacks the capacity to handle milliseconds.

Here's something you might play with (from the debug (immediate) window):

x = "2005-03-29 16:57:06.007"
y = datevalue(left(x, xlastinstr(x, ".")-1))
? y
3/29/05 
' to show that y is stored in date/time data format
? cdbl(y)
 38440

Function xLastInStr is an A97 'roll your own'

Function xLastInStr(ByVal tstr As String, twhat As String) As Integer
'*******************************************
'Name:      xLastInStr (Function)
'Purpose:   Return location of last instance of a character or phrase.
'Inputs:    Call xLastInStr("the quick brown fox jumped the lazy dog", "the")
'Output:    28 - Location of last occurence of "the"
'*******************************************

Dim i As Integer, n As Integer, tLen As Integer

    n = 0
    tLen = Len(twhat)
    For i = Len(RTrim(tstr)) To 1 Step -1
      If Mid(tstr, i, tLen) = twhat Then
          n = i
          Exit For
      End If
    Next i
    
    xLastInStr = n

End Function

Hope maybe that will be adaptable. - Bob

 

[ckitzman]

Thanks a bunch for the input. So far, just using a short date format Ex. Between #mm/dd/yy# And #mm/dd/yy# seems to work. I have to check through the recordset all the way but so far it seems to be working.

 

[ckitzman]

Thanks everyone.

 

[meloncolly]

well, if it's of interest, I tested my input on a SQL server backend timestamp field using access and got exactly the results predicted - date only from a date/time field.