From: Luiz Horacio on
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


From: Max Kudrenko on
Luiz,

Not sure what DB you're using but assuming it's MS SQL, you can do the
first bit by either CONVERT (only for certain styles) or DATEPART
functions. In the second question, you need to replace "BETWEEN
'2007-05-28' AND '2007-05-30'" with "BETWEEN '2007-05-28' AND
'2007-05-31'" if you want to catch data for '2007-05-30'. If you don't
specify time, it's assumed 00:00.

Hope this helps,
Max Kudrenko


Luiz Horacio wrote:
> 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

From: Tony Proctor on
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(a)iname.com> wrote in message
news:%23MIACjnoHHA.3264(a)TK2MSFTNGP04.phx.gbl...
> 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
>
>


From: Jeff Johnson on
"Luiz Horacio" <lhoracio(a)iname.com> wrote in message
news:%23MIACjnoHHA.3264(a)TK2MSFTNGP04.phx.gbl...

> 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.

If this is a true date field then data is NOT stored with ANY format.
Formats are for display, which is to say, formats are for strings. Numbers
are numbers are numbers are numbers.

> 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...")?

Nope. It's just a fact of life. You'd think after so many iterations of SQL
Server and the like that there would be built-in functions for this but
there aren't. We all have to tack on the 11:59:59 PM, so welcome to the
club.


From: Max Kudrenko on
On May 30, 1:32 pm, "Jeff Johnson" <i...(a)enough.spam> wrote:
> > 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...")?
>
> Nope. It's just a fact of life. You'd think after so many iterations of SQL
> Server and the like that there would be built-in functions for this but
> there aren't. We all have to tack on the 11:59:59 PM, so welcome to the
> club.

Why not just add a day without worrying about the time?

 |  Next  |  Last
Pages: 1 2 3 4 5 6
Prev: Debug VB OCX and DLL
Next: COM Error 80110428 occurred