By Vishal Basumatary in javascript — Mar 24, 2023

Convert JS date time to SQLSERVER datetime

Convert JS DateTime to SQL Server DateTime

If you're working with both JavaScript and SQL Server, you may come across the need to convert JavaScript's Date object to SQL Server's datetime. This conversion can be tricky because the two platforms store dates and times in different formats.

Method 1: Using the TO_DATE Function

One way to convert a JavaScript DateTime to a SQL Server DateTime is to use the SQL Server TO_DATE function. This function takes a string argument in the format of "YYYY-MM-DD HH:MI:SS" and converts it to a datetime value. Here's an example:


DECLARE @js_datetime VARCHAR(23) = '2022-05-13 14:45:00';
SELECT TO_DATE(@js_datetime, 'YYYY-MM-DD HH24:MI:SS') AS sql_datetime;

In the above code, we declare a variable called @js_datetime and set it to the JavaScript DateTime value we want to convert. We then pass this variable and the format string to the TO_DATE function, which returns a SQL Server datetime value.

Method 2: Using the CONVERT Function

Another way to convert a JavaScript DateTime to a SQL Server DateTime is to use the CONVERT function. This function takes two arguments: the target data type and the input expression. Here's an example:


DECLARE @js_datetime VARCHAR(23) = '2022-05-13 14:45:00';
SELECT CONVERT(DATETIME, @js_datetime) AS sql_datetime;

In the above code, we declare a variable called @js_datetime and set it to the JavaScript DateTime value we want to convert. We then pass this variable and the target data type to the CONVERT function, which returns a SQL Server datetime value.

Method 3: Using a Parameterized Query

A third way to convert a JavaScript DateTime to a SQL Server DateTime is to use a parameterized query. This method is particularly useful when inserting or updating records in a SQL Server database using JavaScript. Here's an example:


const js_datetime = new Date('2022-05-13T14:45:00');
const sql_query = 'INSERT INTO my_table (date_column) VALUES (?)';
const sql_params = [js_datetime];

await db.query(sql_query, sql_params);

In the above code, we create a new JavaScript Date object called js_datetime and set it to the value we want to insert into the SQL Server database. We then create a SQL query with a single parameter and pass the js_datetime variable as an array to the db.query() method. This method will automatically convert the JavaScript DateTime to a SQL Server datetime value and insert it into the database.