Date Format Issue

[George-Bowyer]

I think that a hazy light might be starting to peek through the clouds of ignorance...

You keep casting a string into date. You cannot do that

I don't think I've been understanding exactly how and when I was doing that.

So if I now understand this right, the line:

datStartDate1 = Format(datStartDate1, "mm/dd/yyyy")

is taking a date variable, converting it into a string and then converting it back into a date variable - all in the same line of code?

I think I wasn't really understanding properly that Format returns a string - even though I said that I did - I think I thought it just changed how the date was displayed.

My fault for trying to do this at midnight on a Sunday. Less database - more sleep...

 

[Gasman]

My testing example was because I *thought* you could not change a variable's declared type.?
In a way I was correct, as MajP has explained, the date was just being cast back as a date and not as a formatted string.

Sorry if I just confused things even more.

 

[CJ_London]

almost there

is taking a date variable, converting it into a string and then converting it back into a date variable - all in the same line of code?

correct - because you have defined your datStartDate1 as a date datatype.

But when you come to create your sql string the date is converted back into a string using your default country settings (uk) - and what you need is the us format.

Format returns a string - even though I said that I did - I think I thought it just changed how the date was displayed.

depends which format you are talking about. The format function returns a string which cannot be further formatted, the format property leaves the underlying value (remember that is a numeric value) unchanged. Typically you will be seeing dates in tables/queries/forms/reports as formatted by the property.

 

[MajP]

Another thing to add a little confusion is that when you cast (implicitly or explicitly) the vba does its best to try to make a legit date.

So if in US settings and I pass in

dim dtmDate as date
dtmDate = "13/11/2018"

It will automatically convert it to 13 November assuming that is what I meant.
If I do this
dtmDate = "2020"
it will convert it to 7/12/1905 since there are 2020 days since the base date of 31Dec1899.
If I do this
dtmDate = "2020-2" it guesses at 1 Feb 2020

That is why you should use date serial or a #literal date# when you can and not cast a string back into a date. You are likely not to get what you are thinking.

 

[isladogs]

dtmDate = "2020"

I can see it works but its a very odd thing to do!
FWIW, it also works without the quotes though your example of 2020-2 is of course treated as 2018 => 10 July 1905

 

[MajP]

I can see it works but its a very odd thing to do!

That was kind of the whole point. If you implicitly cast a string into a date, you may not get what you expect as the OP found out. VB will try real hard to turn it into a date,

Public Sub testIt()
  Dim dtmDate As Date
  Dim newDate As Date
  dtmDate = #1/5/2019#
  
  newDate = Format(dtmDate, "mm/dd/yyyy")
  Debug.Print newDate
  newDate = Format(dtmDate, "dd/mm/yyyy")
  Debug.Print newDate & vbCrLf

  'Will do its best for a date
  dtmDate = "1/21/2019"
  newDate = Format(dtmDate, "mm/dd/yyyy")
  Debug.Print newDate
  newDate = Format(dtmDate, "dd/mm/yyyy")
  Debug.Print newDate

End Sub

1/5/2019 
5/1/2019

1/21/2019 
1/21/2019

 

[isladogs]

See post #13 where I made similar points