Inserting a datetime type value into SQL database

Question

Currently using pyodbc python module to get some data into a SQL database.

One of the data fields is datetime, and currently the corresponding python variable (which I am trying to load into the SQL database) is formatted like this:

MM/DD/YY HH:MM:SS ##:##

(where the ##:## is an offset to the OS's timezone). Anyways, I am getting the following error:

"The conversion of a nvarchar data type to a datetime date type resulted in an out-of-range value"

I am wondering what my best option is to rectify this. Should I manually edit the python string so that it is in a different format (like YYYY-MM-DD for instance), or is there a SQL conversion function I can use within the INSERT INTO statement? Ultimately, I guess I'm wondering what specifically SQL looks for to convert to datetime so I can adjust my data accordingly.

Thanks!

Please show how you are inserting datetime value in which database that supports the industry language, SQL. Also, you are indicating data fields. Are you using pandas (a separate module that is fundamentally different from general purpose Python)? Please post data and code so we can help. — Parfait
– Parfait, Commented Jun 15, 2020 at 20:09

Nitish Reddy · Accepted Answer · 2020-06-15 18:50:30Z

2

Yes there are SQL conversion functions like select convert(varchar, getdate(), 23) for YYY-MM-DD

select convert(varchar, getdate(), 22)

for MM/DD/YY HH:MM:SS

yes you can use it for INSERT INTO statement like

DECLARE @Tdatetime VARCHAR(MAX) 
SET @Tdatetime = CONVERT( VARCHAR, GETDATE() , 106)
INSERT INTO table_name (column1, column2, ...)
VALUES (@Tdatetime , value2, ...)

edited Jun 15, 2020 at 18:50

answered Jun 15, 2020 at 18:42

Nitish Reddy

213 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Inserting a datetime type value into SQL database

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related