Slickest way to covert Date to literal date? (1 Viewer)

peskywinnets · Nov 12, 2017

I'm using some date criteria in an SQL command ...SQL it needs a literal date.

I live in the UK, so all my dates in access are ina DD/MM/YYYY format ....for the SQL command I need #MM/DD/YYYY#

Now using a combination of left(0, MID() & right() I can construct a literal date from the UK date ...but is there a slicker way?

RuralGuy · Nov 12, 2017

Can you use the Format command? The output is always a String.

Gasman · Nov 12, 2017

I either use

Code:

Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.

and then

Code:

Format([MyDate],strcJetDate)

or just use Format([MyDate],"mm\dd\yyyy")

HTH

peskywinnets · Nov 12, 2017

Gasman said:
I either use

Code:

Public Const strcJetDate = "\#mm\/dd\/yyyy\#" 'Needed for dates in queries as Access expects USA format.

and then

Code:

Format([MyDate],strcJetDate) [/QUOTE] Lovely...that worked a treat - many thanks

Dystonia · Nov 12, 2017

I think the last option may have to be....

'#' & format([MyDate],'mm/dd/yyyy') & '#'

...or else the calculation will be returned as a string instead of a date ?

isladogs · Nov 12, 2017

I think the last option may have to be....

'#' & format([MyDate],'mm/dd/yyyy') & '#'

.

No - this is one of many examples in my code.
See the bit in bold. Its worked fine for almost 10 years

Code:

Private Sub cmdThisYear_Click() 'CR v5285

On Error GoTo Err_Handler

    Me.txtFrom = CStr(Format(GetAcYearStart, "dd/mm/yyyy"))
    strDateFrom = Me.txtFrom
    Me.txtTo = CStr(Format(Date, "dd/mm/yyyy"))
    strDateTo = Me.txtTo
    
    'CR v4503W 02/11/2008 - changed date format to ensure correct display
  [B]  strDateFilter = " AND (DateOfIncident Between #" & Format(Me.txtFrom, "mm/dd/yyyy") & _
        "# AND #" & Format(Me.txtTo, "mm/dd/yyyy") & "#) "[/B]

Exit_Handler:
    Exit Sub

Err_Handler:
    'create error message & log
    strProc = Application.VBE.ActiveCodePane.CodeModule.ProcOfLine(Application.VBE.ActiveCodePane.TopLine, 0)
    PopulateErrorLog
    Resume Exit_Handler
    
End Sub

MarkK · Nov 13, 2017

If you use a parameterized DAO.QueryDef then you don't have to worry about delimiters or formats. The QueryDef handles it, so if you have an action query saved that takes a date as a parameter, all you do is load the QueryDef, supply the parameter, and execute the query, and you are done. No mucking around with delimiters or formats. Consider...

Code:

   with currentdb.querydefs("YourParamQuery")
      .parameters(0) = Me.tbValidDateField  [COLOR="Green"]'no delimiters or formats required[/COLOR]
      .execute
      .close
   end with

...or if you prefer to write the sql directly in your code, you can do...

Code:

private const SQL_UPDATE as string = _
   "UPDATE YourTable " & _
   "SET SupervisorID = p0 " & _
   "WHERE HireDate > p1 "

Sub SetSupervisor(SupervisorID as long, d1 as date)
   with currentdb.createquerydef("", SQL_UPDATE)
      .parameters(0) = SupervisorID
      .parameters(1) = d1
      .execute dbFailOnError
      .close
   end with
end sub

All the delimiters and formats are handled by the QueryDef. To me, this is the slickest way to do this kind of thing, and also the fastest, most reliable, most robust, easiest to troubleshoot, most scalable, simplest to understand, and the list goes on and on...
hth
Mark

peskywinnets · Nov 22, 2017

Gasman said:

I either use

Code:

Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.

and then

Code:

Format([MyDate],strcJetDate)

or just use Format([MyDate],"mm\dd\yyyy")

HTH[/QUOTE]

I now need to include the time element to the above For example, this date/time...

22/11/2017 12:40:00 

...would need to become  #11/22/2017 12:40:00#

I've dabbled with the syntax, but I'm just not getting it right :-(

Many thanks!

Mark_ · Nov 22, 2017

Format([MyDate], "mm/dd/yyyy hh:mm:ss")

Remember, a DATE is stored as a number and treated as a number in most databases. SQL needs you to pass a text string that it can translate based on where its starting point is for dates. Don't get caught thinking "My database stores it as 15/06/2016". Think of it as "My database stores it as 43558.06559334 and I need to translate that gibberish of a number to something people can relate to".

arnelgp · Nov 23, 2017

Format([MyDate], "mm/dd/yyyy hh:mm:ss")

should be:

Format([MyDate], "mm/dd/yyyy hh:nn:ss")

Pat Hartman · Nov 23, 2017

NEVER Format() a date unless you actually need to convert it to a string. Since SQL requires string dates to be in US date format - mm/dd/yyyy, if you creant an SQL STRING, you have to Format() your date. However, if you use a querydef and an argument, you DO NOT have to Format() as long as the control your query references is bound to a Date field or has its Format PROPERTY set to Short Date.

Another NEVER - NEVER use specific date formats in control properties. Always use Short Date since that uses the Windows default for your system. If you only want to show year and month for space reasons, then you would use a specific date format such as "mm/yyyy".

In a querydef:
Select ... From ... Where MyDate = Forms!myform!mydate

In an embedded SQL sring
strSQL = "Select ... From ... Where MyDate =#" & Format(Me.MyDate, "mm/dd/yyyy") & "#"

Mark_ · Nov 27, 2017

arnelgp said:
Format([MyDate], "mm/dd/yyyy hh:mm:ss")

should be:

Format([MyDate], "mm/dd/yyyy hh:nn:ss")

I keep forgetting that.

Slickest way to covert Date to literal date? (1 Viewer)

peskywinnets

Registered User.

RuralGuy

AWF VIP

Gasman

Enthusiastic Amateur

peskywinnets

Registered User.

Dystonia

Access 2002, 2010, 2016

isladogs

MVP / VIP

MarkK

bit cruncher

peskywinnets

Registered User.

Mark_

Longboard on the internet

arnelgp

..forever waiting... waiting for jellybean!

Pat Hartman

Super Moderator

Mark_

Longboard on the internet

Similar threads

Users who are viewing this thread