Re: Date extraction

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I don't know which database you're using, and whether you're reading these
fields as text values or real Date values

Assuming, for now, that you're reading these dates as text strings then the
first problem is a lot easier than you think. VB understands that date
format because it's part of the ISO 8601 standard. For instance:

Private Sub Form_Load()
Dim sDateString As String
Dim dDateTimeValue As Date
Dim dDateValue

sDateString = "2007-05-30 01:55:35"

' Show that we can easily decode this string to a date/time value
dDateTimeValue = CDate(sDateString)
MsgBox "Date/time value is " & CStr(dDateTimeValue)

' Now just show how to extra the date portion
dDateValue = DateValue(CDate(sDateString))
MsgBox "Date value is " & CStr(dDateValue)
End Sub

You can then use your Date variables (not String variables) such as
dDateValue in real comparison tests like:

If dDateValue >= CDate("2007-05-28") And dDateValue <
CDate("2007-05-31") Then

In your second question, did you mean a VB query or a SQL query?

Tony Proctor

"Luiz Horacio" <lhoracio@xxxxxxxxx> wrote in message
news:%23MIACjnoHHA.3264@xxxxxxxxxxxxxxxxxxxxxxx
Hi,

One (in fact two) stupid questions. I have a date/time field in a
database.
Data is, of course, saved as '2007-05-30 01:55:35' format. I have some
queries that include this field, and can't find a clean way to work with
it.

1. How can I extract date part of this field, so that I can get only
'2007-05-30' so that I can make var_AdmDate in var_AdmDate =
rst_anything(1)
return '2007-05-30' instead of '2007-05-30 01:55:35' ?

2. Can I work with date part so a query like "... Where AdmDate between
'2007-05-28' and '2007-05-30' And..." will work without the need to add
hours and minutes (like "... Where AdmDate between '2007-05-28 00:00:00'
and
'2007-05-30 23:59:59' And...")?

I tried a lot of things, ended up with ...left(value,10) for question #1
but
this looks stupid to me. In the same way, on question #2 I ended up with
"... Where AdmDate between '2007-05-28 00:00:00' and '2007-05-30 23:59:59'
And..." , adding hours:min:sec in code, but this looks stupid too...

Thanks,

Luiz Horacio




.

Relevant Pages

  • ADO exception with character combination inside string...
    ... We also have various Delphi7 programs that operate on this database. ... These programs are set up to trap the exception in an ADO operation ... and log the query text that was in use when the exception happened. ... What happens is that if we have a string to store inside the database ...
    (borland.public.delphi.database.ado)
  • Row Level Locking from Excel VBA query
    ... There are multiple users accessing the database at ... query that I am using so that two users will not get the same order ... Dim dbsConn As ADODB.Connection ... Dim connString As String ...
    (microsoft.public.access.queries)
  • Changing ODBC Connection between development and production
    ... Public Function ExecAgingReport(datToDate As String, ... Set db = CurrentDb ' Setting the database as current Database ... pass-through query ... qd.ReturnsRecords = True ' Query does retrieve records ...
    (microsoft.public.access.modulesdaovba)
  • Re: Setting the data type of a new filed in a make table query
    ... I have created a small database that has ... wanted to populate the form with all of the equipment that needed an hour ... reading, display the LAST reading and add ... possible with a Select query, or if I can provide you with some additional ...
    (microsoft.public.access.queries)
  • Re: Mail merge in Access
    ... read-only", I have cheked and neither the query that i am reading from, the database i am ... reading from, or the document that i am writting to is read-only. ... Query, OK... ... This reply is posted in the Newsgroup; please post any follow question or reply in the ...
    (microsoft.public.word.mailmerge.fields)