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
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:
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.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.
now() - '365 days'::interval. But I am afraid that the question targets Microsoft SQL Server instead of PostgreSQL.
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 nitCAST(DATEADD(YEAR, -1, GETDATE()) AS DATE)