UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> SQL Insert To Linked SQL Table - Data Type Not Showing Seconds, Access 2016    
 
   
marinko888
post Jan 16 2020, 10:28 AM
Post#1



Posts: 83
Joined: 9-April 19



Hi,

I have an INSERT SQL statement to a linked SQL Database table (data type is DateTime) and I am using Format function which goes like this:

Format(Now, "yyyy/mm/dd hh:mm:ss")

but as a result, I am getting this:

16.1.2020. 16:13:00.000

So, clearly somehow seconds have been lost in this story...
Can anybody please help me to proper format Now so I can get seconds back...wink.gif

Thank you for your help!
This post has been edited by marinko888: Jan 16 2020, 10:40 AM
Go to the top of the page
 
GroverParkGeorge
post Jan 16 2020, 10:44 AM
Post#2


UA Admin
Posts: 36,515
Joined: 20-June 02
From: Newcastle, WA


Don't use format when trying to MANIPULATE date values.. It converts values to STRINGS. Format() should be restricted to DISPLAY ONLY uses, i.e. in forms or reports where you only want to show dates and times in specific formats.

What is the datatype of the field in the SQL Server table, DateTime or DateTime2?

Which ODBC driiver are you using, the default SQL Server driver that is delivered with Windows, or one of the newer drivers?

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
cheekybuddha
post Jan 16 2020, 10:49 AM
Post#3


UtterAccess Moderator
Posts: 12,079
Joined: 6-December 03
From: Telegraph Hill


CODE
Format(Now, "yyyy/mm/dd hh:mm:ss")
                           ^^

The symbol for minutes in the Format() function should be "nn":

Also, when formatting ofr SQL Server you out to use dashes instead of slashes:
CODE
Format(Now, "yyyy-mm-dd hh:nn:ss")


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
marinko888
post Jan 16 2020, 11:01 AM
Post#4



Posts: 83
Joined: 9-April 19



Thank you for your help but this Format also doesn't work...The field data type is DateTime.
This post has been edited by marinko888: Jan 16 2020, 11:03 AM
Go to the top of the page
 
GroverParkGeorge
post Jan 16 2020, 11:03 AM
Post#5


UA Admin
Posts: 36,515
Joined: 20-June 02
From: Newcastle, WA


I really think you are better off NOT USING FORMAT, period.

It CONVERTS dates and times to STRINGS that "look like" dates.

Which ODBC driver are you using?

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
cheekybuddha
post Jan 16 2020, 11:20 AM
Post#6


UtterAccess Moderator
Posts: 12,079
Joined: 6-December 03
From: Telegraph Hill


@George,

The OP said originally:
>> I have an INSERT SQL statement <<

If it's being built in VBA than I think you are better off USING FORMAT! wink.gif

Otherwise, I agree with you.

d

--------------------


Regards,

David Marten
Go to the top of the page
 
marinko888
post Jan 16 2020, 11:20 AM
Post#7



Posts: 83
Joined: 9-April 19



This is the driver I have used:

[ODBC]
DRIVER=SQL Server
Go to the top of the page
 
GroverParkGeorge
post Jan 16 2020, 11:24 AM
Post#8


UA Admin
Posts: 36,515
Joined: 20-June 02
From: Newcastle, WA


To be honest, I stopped using Format() for anything other than DISPLAYING dates a long time ago. I am not sure why it would matter if it's in VBA if the end result is going to be an INSERT statement. It would be interesting to dig into it, though.




--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Jan 16 2020, 11:25 AM
Post#9


UA Admin
Posts: 36,515
Joined: 20-June 02
From: Newcastle, WA


Download the newest ODBC Driver and use it instead.

The downside to doing that is you'll also need to install it on each computer where your users work with your Access Relational Database Application.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Gustav
post Jan 17 2020, 02:04 AM
Post#10


UtterAccess VIP
Posts: 2,166
Joined: 21-February 07
From: Copenhagen


QUOTE
This is the driver I have used:
DRIVER=SQL Server

I am getting this:
16.1.2020. 16:13:00.000


As milliseconds are returned, it indicates that you receive a text value, not a Date value, probably because the data type in the table is DateTime2, not DateTime.
So, either change the data type of the field to DateTime, or install and use a newer driver, Microsoft ODBC Driver (13 or) 17 for SQL Server.

Also, Format is not needed:

CODE
Insert Into YourTable (SomeField, YourDateField) Values (SomeValue, Now())

Go to the top of the page
 
cheekybuddha
post Jan 17 2020, 05:23 AM
Post#11


UtterAccess Moderator
Posts: 12,079
Joined: 6-December 03
From: Telegraph Hill


Good to see you back, Gustav. :hattip;

I concede to you and George that Format() is not required if you use the server's NOW(), or you use an Access query

I always try and fire off literal SQL with the parameters/values resolved before execution, and then it is worth formatting dates in a way that the server expects!

eg. trying:
CODE
  Dim strSQL As String

' Me. txtDate contains 08/01/2020 23:45
  strSQL = INSERT INTO DateTable (MyDate) VALUES ('" & Me.txtDate & "');"
  CurrentDb.Execute strSQL, dbFailOnError
' Or even
  strSQL = INSERT INTO DateTable (MyDate) VALUES ('" & Now & "');"
  CurrentDb.Execute strSQL, dbFailOnError

may yield unexpected results.

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
AlbertKallal
post Jan 17 2020, 07:15 PM
Post#12


UtterAccess VIP
Posts: 2,932
Joined: 12-April 07
From: Edmonton, Alberta Canada


Well, now() or date() as a expression in the query is fine.

but for EVERY other case? Yes, format is darn near requried, else your regional settings will cause issues.

So:

From the access client side?

Yes, you most certainly need to format the date, and MUST use USA format. If you don't, then regional settings will come into play.

To send a datetime for a SQL server, (or a local table), one thus needs to format ALWAYS as USA format, but also include the # as a delimiter.

Failure to do either of the above will result in code that can well break or not behave correctly if end users have different regional settings on their computer.

So, this should work for dates:

CODE
Public Function qudate(dt As Date) As String

   qudate = "#" & Format(dt, "mm/dd/yyyy") & "#"
  
End Function


For date + time then this will work:

CODE
Public Function quDateT(dt As Date) As String

   ' return formatted date
  
   quDate = "#" & Format(dt, "mm\/dd\/yyyy HH:NN:SS") & "#"
  
  
End Function


As several pointed out "NN" is used here for minutes, and not MM

If one is going to use “now()” or “date()” right in the SQL, then fine, no problem (but for all other cases?).


But to insert a value as date time from some control or any other thing THEN now() or date()?

Yes – you need format.

So, to insert, you would need this:

CODE
Sub MyInsExmaple()

   Dim strSQL        As String
   Dim dtLastVisit   As Date
  
   dtLastVisit = Now() ' or some date/time from a control etc.
  
   strSQL = "INSERT INTO tblHotels (FirstName, City, LastVisit) " & _
            "VALUES ('Albert', 'Edmonton'," & quDateT(dtLastVisit) & ")"
            
   CurrentDb.Execute strSQL, dbFailOnError
  
End Sub

So for any VBA var with a date, any text box control that you add a date?
Better use format.

And if the now() or date() is part of a string concat and NOT a expression? Then again one MUST use USA format, and MUST surround the result with #

For linked tables to SQL server, the ODBC driver takes care of this, and again above is the recommended approach.

If one is creating a pass-though query, then I have quSQL(), qudateSQL(), qudateSQLt() so all the same names, but with a S on the end.

So for a PT query, then I would use this:
CODE
Public Function qudateSQLt(myDate As Variant) As String

   ' returns a formatted string of date as T-SQL (SQL SERVER)
   ' format is yyyy-mm-dd HH:NN:SS as required for T-SQL, regardless of date setting
  
   If IsNull(myDate) = True Then
      qudateSQLt = ""
   Else
      ' use ISO date format
      qudateSQLt = "'" & Format(myDate, "yyyy-mm-dd HH:NN:SS") & "'"
   End If
  
End Function


So while we might all be in agreement that using Now() or Date() as a expression in a query is fine and dandy?

Sure, that is all ok.

However, for any other code in an application, the USA format and # as delimiters is a near all encompassing requirement, and without such a design, then local regional settings on the computer are going to mess things up in a really great way.

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
 
FrankRuperto
post Jan 17 2020, 08:10 PM
Post#13



Posts: 520
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


Hi Marinko,

QUOTE
but as a result, I am getting this:

16.1.2020. 16:13:00.000

I noticed the date delimiters in your output are periods. Is your Windows regional settings set to a Eurpoean locale?

Also keep in mind that when you use VBA to concatenate dates into an SQL string, you must use a standard U.S. date format, regardless of the locale set in Windows Regional Settings.

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th January 2020 - 09:31 PM