Doubt in the use of Dateformat in linked server (Linked)

Asked

Viewed 28 times

0

I have the following situation.

Executing that script:

SELECT CONVERT(DATETIME, CONVERT(VARCHAR, 12) + '/' 
                         + CONVERT(VARCHAR, 31) + '/' 
                         + CONVERT(VARCHAR, 1900) + ' ' 
                         + CONVERT(VARCHAR, 0) + ':' + CONVERT(VARCHAR, 0) 
                         + ':' + CONVERT(VARCHAR, 0))

I get the following error:

Message 242, Level 16, Status 3, Line 2 The conversion of a type of data scan on a data type datetime resulted in an off value of halftime.

If you add the command Set Dateformat mdy the script works properly:

31/12/1900 00:00:00

Doubt:

I’m making a update in a linked server table, it has a trigger where there is the script quoted above. Then I try to put the dateformat before the update, but returns the cited error. I realize this happens because I am logged in to one session and the linked server is in another and so returns the error.

How can I treat this ? bearing in mind that I cannot change the trigger of the linked server (Linked)

**Remembering that the two users in each server are with the Portuguese language .

sql sql-server

  • Wouldn’t it be easier to use the Convert(datetime, '20200101 03:04:21',103)? So you don’t need to use the Dateformat

  • @Marcosacr the problem is that I can’t change this script because it’s an ERP of the company I’m looking for via Linked. Strange is that if I run inside the server I do Linked works, but running via Linked accuses the date error.

1 answer

0

You are setting the date in month/day/year format. We know this because the first component is 12 (obviously December), the second component is 31 (the last day of December), and the third is the year (1900). It turns out that it is not legal to convert dates in this format as it will depend on the server configuration. A server in the American format will understand, another server configured for the Brazilian format will not understand.

The best way is to always use the form called "canonical", that is, year, month and day, which will always be understood, regardless of configuration:

SELECT CONVERT(DATETIME, CONVERT(VARCHAR, 1900) + '/' 
                         + CONVERT(VARCHAR, 12) + '/' 
                         + CONVERT(VARCHAR, 31) + ' ' 
                         + CONVERT(VARCHAR, 0) + ':' + CONVERT(VARCHAR, 0) 
                         + ':' + CONVERT(VARCHAR, 0))

Another possibility is to put as the third parameter of the CONVERT function, the desired conversion format. To understand how month/day/year, the code would be: 101.

SELECT CONVERT(DATETIME, CONVERT(VARCHAR, 12) + '/' 
                         + CONVERT(VARCHAR, 31) + '/' 
                         + CONVERT(VARCHAR, 1900) + ' ' 
                         + CONVERT(VARCHAR, 0) + ':' + CONVERT(VARCHAR, 0) 
                         + ':' + CONVERT(VARCHAR, 0),
               101)

So using a conversion from an unconventional format without making the desired format explicit is not a good practice, because it depends on the server configuration. The script should be robust enough to run the same way every time. Then either place your date in canonical form, or explain the desired format. Both solutions should work.

https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.