0

I'm writing a SQL script to register components in a database. One of these requires DateTime (from last year) to be inserted as one of the values into a table, like this:

INSERT INTO ComponentTable (ComponentId, Setting, [Value]) VALUES ('id', 'StartDate', ... )

The C# I had previously been using to insert the value was DateTime.Today.AddDays(-365).ToString() (before switching to a database).

Is there a way of adding the same thing into the SQL script above?

Thanks

Jordan1993
  • 864
  • 1
  • 10
  • 28
  • 1
    Does this answer your question? [Subtract one day from datetime](https://stackoverflow.com/questions/15631977/subtract-one-day-from-datetime) – Silvermind Apr 03 '20 at 10:12
  • Same goes for year – Silvermind Apr 03 '20 at 10:13
  • 3
    the thing I'm most concerned about here is the `ToString()` - that's just asking for problems, and strongly suggests that you're also likely to be introducing SQL injection holes (and i18n/l10n errors); dates should be sent as datetime parameters, not forced into strings; let me know if you need help with that - this is a **VERY IMPORTANT** thing, not just a nit – Marc Gravell Apr 03 '20 at 10:15
  • `CAST(DATEADD(YEAR, -1, GETDATE()) AS DATE)` – Bridge Apr 03 '20 at 10:31

2 Answers2

1

This article shows and explains the SQL Server date/time functionality you need:

Date and Time Data Types and Functions (Transact-SQL)

In your case, this might work:

INSERT INTO ComponentTable (ComponentId, Setting, [Value]) VALUES ('id', 'StartDate', DATEADD(DAY, -365, GETDATE()))

Note the following:

  1. Previously, you determined the date/time value in your client app (C#), thus using the date/time value of your client computer. When using the SQL Server functionality, the system date/time of the machine running your SQL Server instance will be used instead. (I consider this to be an advantage.)
  2. It is advised to store date/time values in the database in a way that they are not region-specific. So including the time zone might be important if you have multiple clients in different time zones. In my opinion, storing the UTC date/time values in the database (by using GETUTCDATE() instead of GETDATE() and converting the retrieved value in your C# client apps to local time (using the DateTime.ToLocalTime() function) might be useful.
Bart Hofland
  • 3,700
  • 1
  • 13
  • 22
0

I tried the following, this gives you the date 365 days ago; is this what you want?

CREATE TABLE test (ID serial PRIMARY KEY, startdate DATE);
INSERT INTO test (startdate)VALUES (date(now()) - integer '365');

See the docs: https://www.postgresql.org/docs/9.0/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

If you want exactly one year (taking care of leap years) then you would probably need to use 'extract' to get the year, then subtract one, and reconstruct the date.

Paul Rene
  • 680
  • 4
  • 14
  • 1
    When using PostgreSQL, this would be fine, I guess. However, I would personally prefer `now() - '365 days'::interval`. But I am afraid that the question targets Microsoft SQL Server instead of PostgreSQL. – Bart Hofland Apr 03 '20 at 11:03
  • Sorry about that... I'm afraid I'm not familiar with Microsoft SQL Server :-( – Paul Rene Apr 03 '20 at 11:08