how to convert timestamp in python to overcome 1..12 issue

Question

I am trying to execute a sql query with Python code and am getting error month muct be in 1..12

I have surfed the net but couldn't find anything related to this issue.

sql = '''
        INSERT INTO {Database}.{Schema}.Instance (
              Id
            , DateTime
            , Period
            , StatusId
        ) VALUES (
            ?,
            CURRENT_TIMESTAMP,
            ?,
            ?
        );
    '''
self['_repository'].execute_query(sql, str(self.id), period, Instance.INITIALISED)

When I am executing the code it gives me an error "month must be in 1..12"

looks like a duplicate to https://stackoverflow.com/a/12957690/9216722 . You have to convert CURRENT_TIMESTAMP to format which datetime type supports. — arunp9294, Oct 31 '19 at 05:43
whats the format of CURRENT_TIMESTAMP you are inserting to table? — arunp9294, Oct 31 '19 at 06:03
It's Azure SQL DB. I need to insert Y-M-D H:M:S:f. How to incorporate this change in the code to avoid the error. — Sathya, Oct 31 '19 at 06:14

score 0 · Answer 1 · answered Oct 31 '19 at 06:24

0

sql = '''
        INSERT INTO {Database}.{Schema}.Instance (
              Id
            , DateTime
            , Period
            , StatusId
        ) VALUES (
            ?,
            CAST(CURRENT_TIMESTAMP AS DateTime)
            ?,
            ?
        );
    '''
self['_repository'].execute_query(sql, str(self.id), period, Instance.INITIALISED)

Can you try this?

answered Oct 31 '19 at 06:24

arunp9294

767
5
15

Same error, 2019-10-31 06:28:33,349 ERROR scheduler.main month must be in 1..12 2019-10-31 06:28:33,349 ERROR month must be in 1..12 – Sathya Oct 31 '19 at 06:34

Adam Strauss · Answer 2 · 2019-10-31T11:49:17.260

0

Let me try to help you out.

from datetime import datetime

timestamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
sql = '''
    INSERT INTO {Database}.{Schema}.Instance (
          Id
        , DateTime
        , Period
        , StatusId
    ) VALUES (
        ?,
        '''
        +
        timestamp
        +
        '''
        ?,
        ?
    );
'''
self['_repository'].execute_query(sql, str(self.id), period, Instance.INITIALISED)

edited Oct 31 '19 at 11:49

answered Oct 31 '19 at 06:51

Adam Strauss

1,889
2
15
45

Thanks Adam, but still getting error ERROR Invalid column name 'timestamp'. – Sathya Oct 31 '19 at 11:28
Need help on this please – Sathya Oct 31 '19 at 11:28
use sting concatenation like '''string'''+timestamp+'''string''' in your query.. – Adam Strauss Oct 31 '19 at 11:46
can you give me the query please – Sathya Oct 31 '19 at 11:50
what is the field of date? Is it timestamp or DATE? – Adam Strauss Oct 31 '19 at 12:15
Apologize for late reply. It is datetime2 datatype. – Sathya Oct 31 '19 at 21:49
Hi Adam, INSERT is working fine now. But when I am using SELECT statement, the error pops up when I am trying to get the datetime field. Datetime coulmn is in datetime2 datatype. – Sathya Nov 01 '19 at 01:29
I tried CAST(,datetime2) , CAST(,datetime2(7) both didn't work – Sathya Nov 01 '19 at 01:58

how to convert timestamp in python to overcome 1..12 issue

2 Answers2