Dates

Dates are stored in SQL with date & time component.

It appears that regardless of the format, if access sees a date stored with other than a time component of 12:00:00 am it will display the time part.

In the instances where we automatically store a date into SQL Server by GETDATE() or similar I need to ensure the time component is per above.

 

SQL Server lives on MDY format, queries assembled in Access with date variables wil be passed as dmy but processed as mdy.

Usually I use SET DATEFORMAT DMY;sql query here but I can't do this in an MSaccess rowsource, so I must do a VB format(date,"mm/dd/yyyy")

Update: format ddmmmyyyy is the most robust.. ie cannot be misinterpreted..

 

In SQL: convert(nvarchar,getdate(),106)

 

 

      sql = "SELECT * FROM vwrptSignificantValue " _
                     & " WHERE acAcceptedTransferDate >= CONVERT (smalldatetime, '" & Format(YearSelectionStartDate, "dd/mm/yyyy") & "' , 103 )" _
                     & " AND acAcceptedTransferDate < DATEADD(day,1, CONVERT (smalldatetime, '" & Format(YearSelectionEndDate, "dd/mm/yyyy") & "' , 103 ))" _
                     & " acValuationCompletedDate IS NOT NULL" _
                     & " AND (acDeaccessionedDate IS NULL OR acDeaccessionedDate >= DATEADD(day,1,CONVERT (smalldatetime, '" & Format(YearSelectionEndDate, "dd/mm/yyyy") & "' , 103 )) )" _
                     & " AND (acOffice = '" & OfficeSelection & "' OR UPPER('" & OfficeSelection & "') = 'ALL')" _
                     & " AND ABS(acTotalValue) >= " & cmbThreshold

 

http://www.sql-server-helper.com/tips/date-formats.aspx