Slickest way to covert Date to literal date?

[peskywinnets]

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]

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

 

[Gasman]

I either use

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

and then

Format([MyDate],strcJetDate)

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

HTH

 

[peskywinnets]

I either use

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

and then

Format([MyDate],strcJetDate)
[/QUOTE]

Lovely...that worked a treat - many thanks

 

[Dystonia]

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]

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

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]

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...

   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...

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]

I either use

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

and then

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_]

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]

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

should be:

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

 

[Pat Hartman]

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_]

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

should be:

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

I keep forgetting that.