Date Format VB6

[Carmell]

The value of my dates in my database is ="MMDDYYYY"
I insert it using this code:

replace(convert(varchar(10), date, 101), '/', '')

it returns like this output

06072018

And now I don't know how select this kind of output using datepicker?

rs1.Open "SELECT * FROM tbl_hourlysalesdetails WHERE Date='" & Format(DTPicker1.Value, "MMddyyyy") & "' and posno='" & Combo1.Text & "' ", cn, 1, 3

on debug mode the format is still mm/dd/yyyy ...
because i want to check on my database if that date is existing..

please help me

[Phill.W]

> "The value of my dates in my database is ="MMDDYYYY""

I think you mean the format of your dates.

Here's a very important truth about Dates in Databases:

Dates have no format.

(Well, OK, they do but, being defined by the IEEE, its not one that the likes of you or I need to overly worry about).

If your dates are persisted in a particular format, it's probably because you're storing them as Strings (chars), which is a very, very Bad Idea.

According to your table name ("tbl_hourlysalesdetails"), I would surmise that you're going to want to do Date-related operations on this data. For that reason (and in order to save your Sanity), you must change your table so that dates are stored in Date columns; it will save you lots and lots of trouble later on.

As far as querying the table goes, you really should be using Parameterised Queries (which, assuming your dates really are Dates) will take care of any required date-formatting for you but, failing that, I'd suggest using a portable date format that's immune to the vagaries of individual Users' Regional settings:

sSQL = "SELECT field_1, field_2*, ... " _
   & "FROM tbl_hourlysalesdetails " _
   & "WHERE Date='" & Format(DTPicker1.Value, "yyyy-mm-dd") & "' " _
   & "and posno='" & Combo1.Text & "' "

* Do not use "select *" in code; it can lead to unexpected performance problems. Always specify the individual fields that you need.

Regards, Phill W.

[Zvoni]

Or use Long-Integer-Datatype for your Datecolumn.
If you need Date with Time use Double instead

[Phill.W]

Zvoni,

Sorry, but I have to disagree with this advice.

Whilst, admittedly, date values in VB can be used in this form, you should not make the assumption that your DBMS(s) will do the same. Furthermore, the date functions that the O.P. will inevitably need to use will require date arguments, not numeric ones.

There are few, if any, advantages in storing date values in any other data type these days and many disadvantages to doing so.

Store date values in Date columns and use date functions to manipulate them.

Regards, Phill W.

[Zvoni]

Phil,

whatever suits you. *shrug*

Considering a Date/Time IS in fact internally a Double (resp. a Integer when not needing time) i don't understand your argument with the OP needing date-functions.

Needing them where? in the Database? How? As a stored procedure? a Trigger?

Sub main()
Debug.Print Now
Debug.Print CDbl(Now)
Debug.Print CDate(CDbl(Now))
End Sub

So, in my case i'm able to just say: Take today and add 60 days in this way:
43258,6481944444 + 60
Result: 43318,6481944444
Well, what do you know?
My result is: 06.08.2018 15:35:56

No need for those pesky Date-Functions in VB
(And if i really need those Date-Functions (DateAdd and sisters), i cast it to a temprary Date-Variable and afterwards back)

My main reason for using Integer/Double:
No need to bother with SQL-Syntax-Differences between different Database-Systems

[Phill.W]

> "No need to bother with SQL-Syntax-Differences between different Database-Systems"

Really?

From the original post:

rs1.Open "SELECT * FROM tbl_hourlysalesdetails WHERE Date='" & Format(DTPicker1.Value, "MMddyyyy") & "' and posno='" & Combo1.Text & "' ", cn, 1, 3

Looks pretty "SQL-syntax-y" to me.

Regards, Phill W.

[Zvoni]

If his datatype in the DB were an integer, his sql would be something like
......... WHERE dbDate=" & CLng(DTPICKER1.Value)

no need to format anything
The syntax i mentioned was about e.g. Access Datetype you have to encase with a #
another advantage: you don't have to bother with Date-representation
germany: dd.mm.yyyy
USA: mm/dd/yyyy
etc.

[PlausiblyDamp]

Phil,

whatever suits you. *shrug*

Considering a Date/Time IS in fact internally a Double (resp. a Integer when not needing time) i don't understand your argument with the OP needing date-functions.

Needing them where? in the Database? How? As a stored procedure? a Trigger?

Sub main()
Debug.Print Now
Debug.Print CDbl(Now)
Debug.Print CDate(CDbl(Now))
End Sub

So, in my case i'm able to just say: Take today and add 60 days in this way:
43258,6481944444 + 60
Result: 43318,6481944444
Well, what do you know?
My result is: 06.08.2018 15:35:56

No need for those pesky Date-Functions in VB
(And if i really need those Date-Functions (DateAdd and sisters), i cast it to a temprary Date-Variable and afterwards back)

My main reason for using Integer/Double:
No need to bother with SQL-Syntax-Differences between different Database-Systems

That kind of code is exactly why a lot of people had issues upgrading to .Net, when you write code relying on internal implementation details you either prevent the language evolving or suffer issues when the internals change.

I personally would have found code like

Dim d as Date = Now()
d = DateAdd("h", 3, d)

a lot more readable than either calculating the correct value for a double to use instead of 3 hours or converting backwards and forwards between a date and a double, especially if sometimes I am adding and subtracting as dates and other times as numbers.

Readability suffers massively when you are playing tricks with undocumented behaviour.

Also, given the fact he is storing these things into a database, it makes sense to use the native types anyway. Store dates as dates and you can sort, search etc. over them using date functionality. Store them as strings or numbers and everything becomes far more complicated and harder to understand.

[vb6forever]

I agree that one should use the "date type" to store dates within the database.
While the "date type" is actually stored internally in the DB as a double, using the date type allows easy date
insertion and retrieval.
The one area where the "date type" can fail is when one is "seeking" a specific date/time record.
If one has a "NoMatch" criteria based on the seek, and "Adds" a new record, two records will exist within
the DB which appear to have the same date BUT in fact are different. This is because of the number of significant digits and subsequent rounding that occurred when the date was stored in the DB. Using a BETWEEN criteria resolves this problem.

[Elroy]

The one area where the "date type" can fail is when one is "seeking" a specific date/time record.
If one has a "NoMatch" criteria based on the seek, and "Adds" a new record, two records will exist within
the DB which appear to have the same date BUT in fact are different. This is because of the number of significant digits and subsequent rounding that occurred when the date was stored in the DB. Using a BETWEEN criteria resolves this problem.

Hmmm, that's sure a confusing statement to me. I think this has already been said, but the Date(type) is just a special case of an IEEE Double. Nothing more, nothing less.

Now, IEEE Doubles can perfectly store integers in the range of -4503599627370496 to +4503599627370496. Anything larger or smaller, and you start to lose precision. Now, dates are indexed from #12/30/1899#. So negative starts going backwards from that date, and positive goes forward from that date.

If a database has IEEE Doubles with no fractional part, a .Seek on that date should work with .NoMatch.

Now, the problem comes in when you start using the fractional part of an IEEE Double representing a date. That fractional part is the specific "time-of-day", with midnight meaning no-fractional-part. Therefore, if you've got a record in a database with a Date field representing 12/27/2014 06:00:00 AM, it will not match when searching for #12/27/2014#. The reason is that 12/27/2014 06:00:00 AM = 42000.25 (when viewed as a Double) and #12/27/2014# = 42000.00 (when viewed as a double). And 42000.25 <> 42000.00.

However, I use .Seek and .NoMatch with dates that came out of the DatePicker all the time, and never have any problems. The DatePicker will never return a non-integer IEEE Double. It always returns integers (in Doubles), so you're safe.

And yes, I second (or fifth, or whatever) that dates should be stored using the Date(type) declaration. That way, you can worry about formatting way later, while the database maintains perfect integrity.

All The Best,
Elroy

[SamOscarBrown]

Poor old Carmell...what IS he (she?) to make of all this bickering among professionals?

As OP seems to be relatively new to VB6 (I only say that because he/she IS trying to use STRINGS for dates...someone above said, "very, very bad idea"), why not just give him 'fair' advice to get him on his way? Like originally proposed by Phil in post # 2. I dare say we can leave the other options out there, but OP just needs to get going....whatcha all think?

[vb6forever]

Elroy:
Sorry you did not like my wording.

While I've never used the DatePicker, I've run into many an issue using Date/Time stamped date types with "Seek" and "NoMatch" because of the double precision issue. In particular, if I store a date into the DB as a PrimaryKey using my computer, and then try to edit that record by using "Seek" and "NoMatch" with what s/b the same record from a server.

SamOscarBrown:
For the most part agree with your comment, except that this date type area can cause a lot of problems and cause a lot of needless effort if one is not aware of what is causing the problem. Hopefully the OP is now at least aware.

[Elroy]

@vb6forever: No problem. I was just thinking a bit of clarity was in order. I've got a database with probably 100 tables or more, and a Date field is part of the indices and relationships on over half of them, and it's worked flawlessly for years, and I virtually always use .Seek "=", aCode, aDate to find child records.

And, on Sam's advice, I'm out'a this one.

Carmell, good luck with it.

Elroy