Node-mssql: Need to pass CURRENT_TIMESTAMP with execute()

[GridTrekkor]

How can I pass CURRENT_TIMESTAMP when using .execute()? I need the current timestamp with no timezone offset; the data in my DB is stored in local time (not UTC) with no timezone. I can use new Date()and remove the offset, but node-mssql still constructs the date with an offset of +00:00 that looks like this:

2019-02-27 09:14:31.6660000 +00:00

Additionally, I would prefer to use CURRENT_TIMESTAMP so that SQL Server evaluates the date on its own at execution time. Sending a timestamp from JS could potentially cause a slightly incorrect date if there is any delay in executing the stored procedure.

Software versions

• NodeJS: 10.15.1
• node-mssql: 4.3.2
• SQL Server: 2016

[dhensby]

Please use the issue template.

Provide code that is not working so we can help

[GridTrekkor]

I hope this further information helps.

I need to pass CURRENT_TIMESTAMP to SQL Server so the server can evaluate the current timestamp at execution time. The string 'CURRENT_TIMESTAMP' cannot be sent to a DATETIME2 field.

Expected behaviour:

Expected 'CURRENT_TIMESTAMP' should not be a string (NVARCHAR) in the generated SQL.

Using this code:

const request = db.request();
const key = 'Today';
request.input(key, 'CURRENT_TIMESTAMP');

Actual behaviour:

Generated SQL looks like this:

exec sp_executesql N'declare @___return___ int;exec @___return___ = [dbo].[MySproc] @Today=@P1;select @___return___ as ''___return___'';',N'@P1 nvarchar(34)',N'CURRENT_TIMESTAMP'

This causes an error because the @Today param in the stored procedure is DATETIME2. This is the returned error:

[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Error converting data type nvarchar to datetime2.

Configuration:

Driver={ODBC Driver 13 for SQL Server}
Trusted_Connection=yes

Software versions

• NodeJS: 10.15.1
• node-mssql: 4.3.2
• SQL Server: 2016

[dhensby]

Thanks

[dhensby]

That's interesting, I'm not sure how to pass internal DB values as (@patriksimek any hints?)

How would one do this in a raw SQL query?

[GridTrekkor]

Correct, it's a SQL reserved word. A raw query might look like this:

DECLARE @CurrentTimestamp DATETIME2 (0) = CURRENT_TIMESTAMP;
EXECUTE [dbo].[MySproc] @Today=@CurrentTimestamp;

Result: 2019-02-27 12:57:20

[dhensby]

OK - I'd like to wait to hear from @patriksimek about this, but it may be that we need to modify the datetime Type to allow current_timestamp as an allowed value which is then passed through literally...

For now, I suspect it just is not possible.

[patriksimek]

I generally don't like the idea of passing a keyword as a value of a parameter. To execute a modified statement, you can always use the query or batch methods.

db.request().query('EXECUTE [dbo].[MySpro] @Today = CURRENT_TIMESTAMP')

[dhensby]

Ok. So you might be able to achieve this as a custom type, but I don't think we'll be providing support for this as it stands

[GridTrekkor]

@patriksimek This is disappointing because I'm using .execute() as a safe and reliable way to build the query, which takes care of parameter strings and numbers automatically. Is there a way to get access to the generated SQL before running .execute()?

[dhensby]

@GridTrekkor have you tried using a custom type to allow you to use DBMS constants?

[patriksimek]

@GridTrekkor You can pass sanitized values as parameters to your queries as well.

[GridTrekkor]

@dhensby I'd like to revisit this. Can you point me in the direction of what you have in mind?

[dhensby]

@GridTrekkor to be honest, I don't have much in mind - I'm not at all sure of the best way to achieve this.

[GridTrekkor]

@dhensby Here's my solution for now. I use moment to construct a datetime string and pass that to SQL Server.

moment().tz('America/Chicago').format('YYYY-MM-DD HH:mm:ss.SSS');

This forces the resulting datetime string to be in the timezone I need. But of course this is a workaround, and I'd prefer not to do it.

@patriksimek What is your objection to passing a SQL keyword?

[dhensby]

In my opinion, that's not a "work around" it's the right way to do it.

You shouldn't really be relying on the DB server to have a time that is in sync with the application server. This also makes your code more testable (you can mock times) and it has other potential advantages (like not breaking the query cache) compared to using CURRENT_TIMESTAMP.

[patriksimek]

@GridTrekkor Parameters are here to pass values, not keywords. It just doesn't feel right to do that.