Date Formatting in VB6

rasinc · Joined: 04 Oct 2007 Posts: 24

I am working with a file that has the date in yyyy-mm-dd format and need to
convert any dates I am working with to that format for comparison purposes
and for use in SQL queries. I have tried to use

dtToday = Format(Date, "yyyy-mm-dd")

to put it in the proper format but I still get mm/dd/yyyy. Can anyone give
me an idea on what syntax I am supposed to use? The help indicates I am
doing it correctly.

TIA

Archived from group: microsoft>public>vb>syntax

"Rick Rothstein \ · Joined: 04 Oct 2007 Posts: 1584

>I am working with a file that has the date in yyyy-mm-dd format and need to
> convert any dates I am working with to that format for comparison purposes
> and for use in SQL queries. I have tried to use
>
> dtToday = Format(Date, "yyyy-mm-dd")
>
> to put it in the proper format but I still get mm/dd/yyyy. Can anyone
> give
> me an idea on what syntax I am supposed to use? The help indicates I am
> doing it correctly.

Change the data type of dtToday from Date to String. In VB, Date data types
do not have any format to them... they are stored as Doubles (the whole
number represents the number of days away from date-zero, December 30, 1899
and the fractional part represents the fractional part of a day represented
by the Time portion, if any). VB presents Date data to you, formatted
according to the computer's Regional Settings... so, you can't force a
format onto a Date data type. String values, on the other hand, can hold the
format being returned by the Format function, so you can compare String
values to do what you have indicated. However, there is another way at
this... leave the return value from the Date function as is and convert the
file's date (which is a String value) to a VB Date instead (use the
DateSerial function after pulling the file's date apart) and then compare
those as Dates.

Rick

rasinc · Joined: 04 Oct 2007 Posts: 24

Thanks for the suggestion but while I was able to get the string to convert
to the correct format, the comparison is being done directly in an SQL
statement during the retrieval rather than after and comparing in the
recordset. I am using this WHERE clause

myCmd.CommandText = "SELECT Id, dtDate, sNum, CustId " _
& "FROM Orders " _
& "WHERE Date (Orders.dtDate) between " & dtYesterday & " and " &
dtToday

The database is a MySQL database where the date is stored in yyyy-mm-dd with
the time. This is why I am using the Date function to remove the time
portion. I thought about converting the string back to date for comparison
but I am back to the original problem. I can hardcode dates and get the
statement to work properly in SQLYog but I cannot get it to work in VB6 with
variables or hardcoded. To hardcode this in VB6, what can I do to put the
dates in? I've tried with double quotes, with #, etc. just to see if I can
retrieve anything without using variables but I am not making any headway.

Anyway, hope this makes sense. Any help is appreciated. TIA

"Rick Rothstein (MVP - VB)" wrote:

> >I am working with a file that has the date in yyyy-mm-dd format and need to
> > convert any dates I am working with to that format for comparison purposes
> > and for use in SQL queries. I have tried to use
> >
> > dtToday = Format(Date, "yyyy-mm-dd")
> >
> > to put it in the proper format but I still get mm/dd/yyyy. Can anyone
> > give
> > me an idea on what syntax I am supposed to use? The help indicates I am
> > doing it correctly.
>
> Change the data type of dtToday from Date to String. In VB, Date data types
> do not have any format to them... they are stored as Doubles (the whole
> number represents the number of days away from date-zero, December 30, 1899
> and the fractional part represents the fractional part of a day represented
> by the Time portion, if any). VB presents Date data to you, formatted
> according to the computer's Regional Settings... so, you can't force a
> format onto a Date data type. String values, on the other hand, can hold the
> format being returned by the Format function, so you can compare String
> values to do what you have indicated. However, there is another way at
> this... leave the return value from the Date function as is and convert the
> file's date (which is a String value) to a VB Date instead (use the
> DateSerial function after pulling the file's date apart) and then compare
> those as Dates.
>
> Rick
>
>
>

Bob Butler · Joined: 04 Oct 2007 Posts: 1081

"rasinc" wrote in message @microsoft.com...
> Thanks for the suggestion but while I was able to get the string to
> convert
> to the correct format, the comparison is being done directly in an SQL
> statement during the retrieval rather than after and comparing in the
> recordset. I am using this WHERE clause
>
> myCmd.CommandText = "SELECT Id, dtDate, sNum, CustId " _
> & "FROM Orders " _
> & "WHERE Date (Orders.dtDate) between " & dtYesterday & " and " &
> dtToday

.... between " & format$(dtYesterday,"yyyy-mm-dd") & _
" and " & Format$(dtToday,"yyyy-mm-dd")

I'm not too familiar with MySql, in SQL Server you'd need single quotes
around the dates so that it'd look like this:
.... between '2007-4-24' and '2007-4-25'

> The database is a MySQL database where the date is stored in yyyy-mm-dd
> with
> the time. This is why I am using the Date function to remove the time
> portion.

If the database stores both dat and time then you may need to change the
query to end with
" and " & Format$(dtToday,"yyyy-mm-dd") & " 23:59:59"

> I thought about converting the string back to date for comparison

Converting what string back to date? You need to format a string with the
date to pass in the command text to the database engine but the recordset
you get back will have date fields. There's a huge difference between a
Date and a String that happens to hold text representing a date.

Tony Proctor · Joined: 04 Oct 2007 Posts: 1051

As Rick said, you have probably typed dtDate as 'Date' rather than 'String'.
That would mean your nicely formatted yyyy-mm-dd text is being converted
back to a binary date, and then re-formatted according to your default date
string when you try to view it.

Try the following to see what I mean...

Dim sDate As String, dDate As Date

sDate = Format$(Date, "yyyy-mm-dd")
Debug.Print sDate

dDate = Format$(Date, "yyyy-mm-dd")
Debug.Print dDate

Also, to get rid of a time portion in VB, use the DateValue function, e.g.
DateValue(Now()) returns exactly the same as the VB Date() function

Tony Proctor

"rasinc" wrote in message@microsoft.com...
> Thanks for the suggestion but while I was able to get the string to
convert
> to the correct format, the comparison is being done directly in an SQL
> statement during the retrieval rather than after and comparing in the
> recordset. I am using this WHERE clause
>
> myCmd.CommandText = "SELECT Id, dtDate, sNum, CustId " _
> & "FROM Orders " _
> & "WHERE Date (Orders.dtDate) between " & dtYesterday & " and " &
> dtToday
>
> The database is a MySQL database where the date is stored in yyyy-mm-dd
with
> the time. This is why I am using the Date function to remove the time
> portion. I thought about converting the string back to date for
comparison
> but I am back to the original problem. I can hardcode dates and get the
> statement to work properly in SQLYog but I cannot get it to work in VB6
with
> variables or hardcoded. To hardcode this in VB6, what can I do to put the
> dates in? I've tried with double quotes, with #, etc. just to see if I
can
> retrieve anything without using variables but I am not making any headway.
>
> Anyway, hope this makes sense. Any help is appreciated. TIA
>
>
> "Rick Rothstein (MVP - VB)" wrote:
>
> > >I am working with a file that has the date in yyyy-mm-dd format and
need to
> > > convert any dates I am working with to that format for comparison
purposes
> > > and for use in SQL queries. I have tried to use
> > >
> > > dtToday = Format(Date, "yyyy-mm-dd")
> > >
> > > to put it in the proper format but I still get mm/dd/yyyy. Can anyone
> > > give
> > > me an idea on what syntax I am supposed to use? The help indicates I
am
> > > doing it correctly.
> >
> > Change the data type of dtToday from Date to String. In VB, Date data
types
> > do not have any format to them... they are stored as Doubles (the whole
> > number represents the number of days away from date-zero, December 30,
1899
> > and the fractional part represents the fractional part of a day
represented
> > by the Time portion, if any). VB presents Date data to you, formatted
> > according to the computer's Regional Settings... so, you can't force a
> > format onto a Date data type. String values, on the other hand, can hold
the
> > format being returned by the Format function, so you can compare String
> > values to do what you have indicated. However, there is another way at
> > this... leave the return value from the Date function as is and convert
the
> > file's date (which is a String value) to a VB Date instead (use the
> > DateSerial function after pulling the file's date apart) and then
compare
> > those as Dates.
> >
> > Rick
> >
> >
> >

"Rick Rothstein \ · Joined: 04 Oct 2007 Posts: 1584

> Try the following to see what I mean...
>
> Dim sDate As String, dDate As Date
>
> sDate = Format$(Date, "yyyy-mm-dd")
> Debug.Print sDate

Just as an interesting aside, your Format statement above can be replaced
with this...

sDate = Mid$(Date$ & "-" & Date$, 7, 10)

I'm just pointing this out in case those following this thread weren't aware
that the Date function and its String counterpart, the Date$ function,
return differently formatted values. The Date function (no $ sign) uses your
Regional Setting for the date separator in its return value where as the
Date$ function always uses a dash as the separator.

Rick

Tony Proctor · Joined: 04 Oct 2007 Posts: 1051

> The Date function (no $ sign) uses your Regional Setting for the date
separator

Actually Rick, the Date() function (no '$') returns a binary date value, and
so has no separator at that point. However, normal formatting using
Format$/CStr/etc then invokes the default date formatting Smile

Tony Proctor

"Rick Rothstein (MVP - VB)" wrote in
message @TK2MSFTNGP06.phx.gbl...
> > Try the following to see what I mean...
> >
> > Dim sDate As String, dDate As Date
> >
> > sDate = Format$(Date, "yyyy-mm-dd")
> > Debug.Print sDate
>
> Just as an interesting aside, your Format statement above can be replaced
> with this...
>
> sDate = Mid$(Date$ & "-" & Date$, 7, 10)
>
> I'm just pointing this out in case those following this thread weren't
aware
> that the Date function and its String counterpart, the Date$ function,
> return differently formatted values. The Date function (no $ sign) uses
your
> Regional Setting for the date separator in its return value where as the
> Date$ function always uses a dash as the separator.
>
> Rick
>
>

"Rick Rothstein \ · Joined: 04 Oct 2007 Posts: 1584

>> The Date function (no $ sign) uses your Regional Setting for the date
> separator
>
> Actually Rick, the Date() function (no '$') returns a binary date value,
> and
> so has no separator at that point. However, normal formatting using
> Format$/CStr/etc then invokes the default date formatting Smile

While I am not exactly sure about your use of the term "binary date value"
(it's the "binary" part I'm stuck on... a Date value is housed in a Double),
you are correct... Date (no $ sign) does not contain the Regionally set
separator character until you **look** at it in some **displayable** form
(for example, execute Print Date in the Immediate window; or assign the Date
function's return value to a String variable). Of course, the Format
function, by its very nature, overrides the Regionally set separator
character with whatever you tell it to use for the separator character.

Rick

Tony Proctor · Joined: 04 Oct 2007 Posts: 1051

> While I am not exactly sure about your use of the term "binary date value"

Sorry Rick, I was using the term "binary" here as the opposite to "text"
(i.e non-printable)

> Of course, the Format function, by its very nature, overrides the
Regionally set separator

Except, of course, in the (useless) cases of Format(Date()), and
Format(Date(),""), or if you're trying to get it to return the current
regional separator on its own

Tony Proctor

"Rick Rothstein (MVP - VB)" wrote in
message $jWt1hHHA.4516@TK2MSFTNGP03.phx.gbl...
> >> The Date function (no $ sign) uses your Regional Setting for the date
> > separator
> >
> > Actually Rick, the Date() function (no '$') returns a binary date value,
> > and
> > so has no separator at that point. However, normal formatting using
> > Format$/CStr/etc then invokes the default date formatting Smile

>
> While I am not exactly sure about your use of the term "binary date value"
> (it's the "binary" part I'm stuck on... a Date value is housed in a
Double),
> you are correct... Date (no $ sign) does not contain the Regionally set
> separator character until you **look** at it in some **displayable** form
> (for example, execute Print Date in the Immediate window; or assign the
Date
> function's return value to a String variable). Of course, the Format
> function, by its very nature, overrides the Regionally set separator
> character with whatever you tell it to use for the separator character.
>
> Rick
>
>

Tony Proctor · Joined: 04 Oct 2007 Posts: 1051

....am I pedantic or what Smile

Tony Proctor

"Tony Proctor" wrote in message@TK2MSFTNGP03.phx.gbl...
> > While I am not exactly sure about your use of the term "binary date
value"
>
> Sorry Rick, I was using the term "binary" here as the opposite to "text"
> (i.e non-printable)
>
> > Of course, the Format function, by its very nature, overrides the
> Regionally set separator
>
> Except, of course, in the (useless) cases of Format(Date()), and
> Format(Date(),""), or if you're trying to get it to return the current
> regional separator on its own
>
>
> Tony Proctor
>
> "Rick Rothstein (MVP - VB)" wrote in
> message $jWt1hHHA.4516@TK2MSFTNGP03.phx.gbl...
> > >> The Date function (no $ sign) uses your Regional Setting for the date
> > > separator
> > >
> > > Actually Rick, the Date() function (no '$') returns a binary date
value,
> > > and
> > > so has no separator at that point. However, normal formatting using
> > > Format$/CStr/etc then invokes the default date formatting Smile

> >
> > While I am not exactly sure about your use of the term "binary date
value"
> > (it's the "binary" part I'm stuck on... a Date value is housed in a
> Double),
> > you are correct... Date (no $ sign) does not contain the Regionally set
> > separator character until you **look** at it in some **displayable**
form
> > (for example, execute Print Date in the Immediate window; or assign the
> Date
> > function's return value to a String variable). Of course, the Format
> > function, by its very nature, overrides the Regionally set separator
> > character with whatever you tell it to use for the separator character.
> >
> > Rick
> >
> >
>
>

"Rick Rothstein \ · Joined: 04 Oct 2007 Posts: 1584

> ...am I pedantic or what Smile

I'll say....

>> > Of course, the Format function, by its very nature, overrides the
>> Regionally set separator
>>
>> Except, of course, in the (useless) cases of Format(Date()), and
>> Format(Date(),""), or if you're trying to get it to return the current
>> regional separator on its own

Okay, let me amend that statement... "Of course, the Format function, by its
very nature, ***CAN*** override the Regionally set separator." Better?

Rick

Paul Clement · Joined: 04 Oct 2007 Posts: 1560

On Wed, 25 Apr 2007 05:14:04 -0700, rasinc wrote:

¤ Thanks for the suggestion but while I was able to get the string to convert
¤ to the correct format, the comparison is being done directly in an SQL
¤ statement during the retrieval rather than after and comparing in the
¤ recordset. I am using this WHERE clause
¤
¤ myCmd.CommandText = "SELECT Id, dtDate, sNum, CustId " _
¤ & "FROM Orders " _
¤ & "WHERE Date (Orders.dtDate) between " & dtYesterday & " and " &
¤ dtToday
¤
¤ The database is a MySQL database where the date is stored in yyyy-mm-dd with
¤ the time. This is why I am using the Date function to remove the time
¤ portion. I thought about converting the string back to date for comparison
¤ but I am back to the original problem. I can hardcode dates and get the
¤ statement to work properly in SQLYog but I cannot get it to work in VB6 with
¤ variables or hardcoded. To hardcode this in VB6, what can I do to put the
¤ dates in? I've tried with double quotes, with #, etc. just to see if I can
¤ retrieve anything without using variables but I am not making any headway.
¤
¤ Anyway, hope this makes sense. Any help is appreciated. TIA

Have you tried using command parameters instead of SQL variable insertion? All that would be
required is a valid Date value in a Date parameter.

Attempting to format a date so that it matches the format on the back end is a PITA and should be
avoided if possible.

Paul
~~~~
Microsoft MVP (Visual Basic)

Tony Proctor · Joined: 04 Oct 2007 Posts: 1051

The yyyy-mm-dd format is part of the ISO 8601 definition Paul. Does MySQL
store it in that format, or merely accept the international format in
queries? I know SQL Server accepts this same format because it's
internationally defined, and it doesn't use that format for storage

Tony Proctor

"Paul Clement" wrote in message@4ax.com...
> On Wed, 25 Apr 2007 05:14:04 -0700, rasinc
wrote:
>
> ¤ Thanks for the suggestion but while I was able to get the string to
convert
> ¤ to the correct format, the comparison is being done directly in an SQL
> ¤ statement during the retrieval rather than after and comparing in the
> ¤ recordset. I am using this WHERE clause
> ¤
> ¤ myCmd.CommandText = "SELECT Id, dtDate, sNum, CustId " _
> ¤ & "FROM Orders " _
> ¤ & "WHERE Date (Orders.dtDate) between " & dtYesterday & " and " &
> ¤ dtToday
> ¤
> ¤ The database is a MySQL database where the date is stored in yyyy-mm-dd
with
> ¤ the time. This is why I am using the Date function to remove the time
> ¤ portion. I thought about converting the string back to date for
comparison
> ¤ but I am back to the original problem. I can hardcode dates and get the
> ¤ statement to work properly in SQLYog but I cannot get it to work in VB6
with
> ¤ variables or hardcoded. To hardcode this in VB6, what can I do to put
the
> ¤ dates in? I've tried with double quotes, with #, etc. just to see if I
can
> ¤ retrieve anything without using variables but I am not making any
headway.
> ¤
> ¤ Anyway, hope this makes sense. Any help is appreciated. TIA
>
> Have you tried using command parameters instead of SQL variable insertion?
All that would be
> required is a valid Date value in a Date parameter.
>
> Attempting to format a date so that it matches the format on the back end
is a PITA and should be
> avoided if possible.
>
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)

Paul Clement · Joined: 04 Oct 2007 Posts: 1560

On Wed, 25 Apr 2007 21:02:37 +0100, "Tony Proctor"
wrote:

¤ The yyyy-mm-dd format is part of the ISO 8601 definition Paul. Does MySQL
¤ store it in that format, or merely accept the international format in
¤ queries? I know SQL Server accepts this same format because it's
¤ internationally defined, and it doesn't use that format for storage
¤

I don't recall every needing to be concerned about the format Tony since the operation involved is
numeric. At least this is the case with Oracle when using a Date type parameter instead of a string
value inserted into a SQL statement that has to be converted by the server's database engine.

If MySQL treats dates differently then I'm not aware of it (which is certainly possible).

Paul
~~~~
Microsoft MVP (Visual Basic)