Product:
Microsoft SQL server 2016
Microsoft Windows 2016 server
Problem:
How add rows for a date to a table?
Solution:
For this example we use this demo database:
Create a table with;
CREATE TABLE [dbo].[Storage]( [Id] [int] IDENTITY(1,1) NOT NULL, [CreationDate] [datetime] NOT NULL, [DisplayName] [nvarchar](40) NOT NULL, [DownVotes] [int] NOT NULL, [LastAccessDate] [datetime] NOT NULL, [Location] [nvarchar](100) NULL, [Reputation] [int] NOT NULL, [UpVotes] [int] NOT NULL, [Views] [int] NOT NULL, [AccountId] [int] NULL, [UpdateDate] [datetime] NOT NULL CONSTRAINT [PK_Storage_Id] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO
To insert one row based on date and time;
INSERT INTO [dbo].[Storage] ( [CreationDate], [DisplayName], [DownVotes], [LastAccessDate], [Location], [Reputation], [UpVotes], [Views], [AccountId], [UpdateDate] ) SELECT [CreationDate], [DisplayName], [DownVotes], [LastAccessDate], [Location], [Reputation], [UpVotes], [Views], [AccountId], (GETDATE()) AS UpdateDate FROM [dbo].[Users] WHERE LastAccessDate = '2018-06-18 11:47:32.200'
To set a variable for a date;
DECLARE @@TargetDate DATETIME = '2018-06-18' SELECT @@TargetDate
To insert all rows for a specific LastAccessDate;
INSERT INTO [dbo].[Storage] ( [CreationDate], [DisplayName], [DownVotes], [LastAccessDate], [Location], [Reputation], [UpVotes], [Views], [AccountId], [UpdateDate] ) SELECT [CreationDate], [DisplayName], [DownVotes], [LastAccessDate], [Location], [Reputation], [UpVotes], [Views], [AccountId], (GETDATE()) AS UpdateDate FROM [dbo].[Users] WHERE CONVERT (date,(LastAccessDate)) = @@TargetDate
More information
https://www.sqlshack.com/how-to-update-from-a-select-statement-in-sql-server/
https://popsql.com/learn-sql/sql-server/how-to-query-date-and-time-in-sql-server