0

I'm trying to add a row to my db from my flask app. I'm using SQLAlchemy. This is the rror I'm getting:

[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Conversion failed when converting date and/or time from character string

For the life of me I CANNOT figure out how to insert the date (both through the use of the ORM and through raw SQL).

I have this SQLAlchemy class:

class ScenarioTrainingSchedule(Base):
    __tablename__ = "scenario_training_schedule"
    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, nullable=False)
    scenario_id = Column(String, nullable=False)
    training_date = Column(Date, nullable=False)
    creation_date = Column(DateTime, nullable=False, server_default="func.now()")

This is the create statement that is executed:

CREATE TABLE [schedule](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [company_id] [int] NOT NULL,
    [scenario_id] [varchar](max) NOT NULL,
    [training_date] [date] NOT NULL,
    [creation_date] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [schedule] ADD PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
ALTER TABLE [schedule] ADD  DEFAULT ('func.now()') FOR [creation_date]
GO

This is the python code:

training = Schedule(company_id=1), scenario_id='practice', training_date=datetime.strptime('2020-01-01', '%Y-%m-%d').date())
session.add(training)

This is the SQL i've tried (among others):

INSERT INTO schedule (company_id, scenario_id, training_date)
VALUES (12, 'practice', DATEFROMPARTS(YEAR(GETDATE()), 1, 1));

WHAT is going on?

RogerKint
  • 454
  • 5
  • 13
  • 1
    `ALTER TABLE [schedule] ADD DEFAULT ('func.now()') FOR [creation_date]` What "date" is the **literal string** `'func.now()'`? – Thom A Jul 17 '23 at 16:00
  • Also, you *really* need to get into the habit of giving your `CONSTRAINT`s names. – Thom A Jul 17 '23 at 16:01
  • Does this answer your question? [SQLAlchemy default DateTime](https://stackoverflow.com/questions/13370317/sqlalchemy-default-datetime) – Thom A Jul 17 '23 at 16:04
  • @ThomA Thanks alot!! Your first comment was enough, changed it to func.now() instead of the string. You can respond with an answer and I'll check it! What do you mean with this "Also, you really need to get into the habit of giving your CONSTRAINTs names" btw? :) Thanks again – RogerKint Jul 17 '23 at 16:10
  • Quite literally *name* your constraints, don't rely on the data engine to give it a meaningless default name. – Thom A Jul 17 '23 at 16:13
  • server_default=func.now(), wouldn't that mean that you will send some original date there. I think it should be something like: server_default = 'GETDATE()' or whatever it should use to set correct call. Or does it actually translates func.now() to a getdate() call – siggemannen Jul 17 '23 at 16:48

0 Answers0