Date extraction
in [Visual Basic]
|
Prev: Debug VB OCX and DLL
Next: COM Error 80110428 occurred
From: Luiz Horacio on 30 May 2007 01:09 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 30 May 2007 04:57 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 30 May 2007 05:09 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 30 May 2007 09:32 "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 30 May 2007 09:52
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? |