SQL Server DATETIME, automatically fill at exactly 10pm of the day when not fill before 10pm of the same day

Question

Table structure:

ID int
status varchar (10)
DATE_CREATED datetime
DATE_CLOSED datetime

Stored procedure for filling DATE_CREATED:

CREATE PROCEDURE [dbo].[...]
    @ID INT,
    @STATUS VARCHAR(10) = 'Open',
    @DATE_CREATED DATETIME = NULL
AS
    SET NOCOUNT ON

    UPDATE table
    SET STATUS = @STATUS,
        DATE_CREATED = COALESCE(@DATE_CREATED, GETDATE())
    FROM table

From that point the column DATE_CLOSED is NULL. I wanted to automatically fill the column with the date of DATE_CREATED column but with the time of 10pm, and it should be filled by 10pm automatically and also the status filled to 'closed'.

"it should be filled by 10pm automatically". What does that mean? — Gordon Linoff, Apr 06 '18 at 00:42

score 0 · Accepted Answer · answered Apr 06 '18 at 00:44

0

You can calculate 10 p.m. on the current date using:

dateadd(hour, 22, cast(cast(getdate() as date) as datetime))

This is easy to add into the update. I'm not 100% sure, though, that this is all you are asking for.

answered Apr 06 '18 at 00:44

Gordon Linoff

1,242,037
58
646
786

Thanks it worked well, lastly how will i set the other column status to a closed at 10pm? – Alphatrix Apr 06 '18 at 01:14
1

You can set up a SQL Server Agent job to run at 10:00 p.m. to set the status. – Gordon Linoff Apr 06 '18 at 01:47

score 0 · Answer 2 · answered Apr 06 '18 at 02:06

If you are looking to schedule a query or stored procedure to run at a set time of the day, then the normal approach is to use SQL Server Agent which has fairly elaborate job scheduling functionality.

Look at this question for an example of how to run a daily job. how to schedule a job for sql query to run daily?

And here's Microsoft's documentation: https://learn.microsoft.com/en-us/sql/ssms/agent/schedule-a-job

SQL Server DATETIME, automatically fill at exactly 10pm of the day when not fill before 10pm of the same day

2 Answers2