i want sql query to create a table to store the current date and time (i.e time is between 9:00 to 16:00 current date )

Question

1.00/5 (2 votes)

See more:

SQL

[utime]  Ltime --col name
[2015-07-21 9:00] 2015-07-21 9:05
[2015-07-21 9:05]  2015-07-21 9:10
[2015-07-21 9:10]  2015-07-21 9:15
[2015-07-21 9:15]  2015-07-21 9:20
[2015-07-21 9:20]   2015-07-21 9:25
........

[2015-07-21 16:00] 2015-07-21 16:05

Posted 21-Jul-15 20:16pm

Member 11844168

Add a Solution

Comments

Kornfeld Eliyahu Peter 22-Jul-15 2:27am

And What have you tried?[^]

Member 11844168 23-Jul-15 0:34am

i have tried only time part (i.e 9:00-9:05 so on) but what about current date???

Wendelius 22-Jul-15 2:46am

IT's not clear what you're after. Do you want to know how to create a table, do you want to generate rows to the table based on a condition or do you want to check that the data entered into the table is between certain time?

Member 11844168 23-Jul-15 0:33am

i want to create a table that this data of current date

Member 11844168 23-Jul-15 1:29am

A table with dynamic date and static time...hope now u got it

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Asif_ · Answer 1 · 2015-07-21T21:23:00

Your requirement is odd as we have no idea how you going to use it but anyway, you can use below script to generate required rows in a table

SQL

DECLARE @TBL TABLE
(
    UTIME DATETIME,
    LTIME DATETIME
)

DECLARE @STRARTDATE DATETIME,
        @ENDDATE DATETIME


SELECT @STRARTDATE = DATEADD(minute, 535, CONVERT(varchar(10), GETDATE(), 120))
SELECT @ENDDATE    = DATEADD(minute, 960, CONVERT(varchar(10), GETDATE(), 120))

INSERT INTO @TBL
SELECT DATEADD(minute, 5*n, @STRARTDATE), DATEADD(minute, (5*n)+5, @STRARTDATE) LTIME
FROM
(
    SELECT TOP (100) n = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id]))
    FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
) a
WHERE DATEADD(minute, 5*n, @STRARTDATE) <= @ENDDATE

SELECT * FROM @TBL

Code Modified as per updated requirement

Wendelius · Answer 2 · 2015-07-21T21:54:00

This is probably related to your previous question so if you just want to fill a table with the 'seed' values for your query consider something like this

SQL

CREATE TABLE Intervals5 (
   intervaltime datetime
);

SET NOCOUNT ON

DECLARE @datevalue datetime = CONVERT( datetime, '2015-07-21T09:00:00.000', 126);
DECLARE @upperlimit datetime = CONVERT( datetime, '2016-07-21T09:00:00.000', 126);
BEGIN
   WHILE @datevalue < @upperlimit BEGIN
      IF (DATEPART(hour, @datevalue) BETWEEN 9 AND 16) OR (DATEPART(hour, @datevalue) = 17 AND DATEPART(minute, @datevalue) = 0) BEGIN
         INSERT INTO Intervals5 (intervaltime) VALUES (@datevalue);
      END;
      SET @datevalue = DATEADD(minute, 5, @datevalue);
   END;
END;


SELECT * FROM Intervals5;

Member 11844168 · Answer 3 · 2015-07-22T21:00:00

-- i got the solution ...any efficient solution then this ???
DECLARE @Today varchar(30), @DateFrom datetime, @DateTo datetime
SET @Today = CONVERT(varchar(10), GETDATE(), 120)
SET @DateFrom = CONVERT(datetime, @Today + ' 09:00:00', 120)
SET @DateTo = CONVERT(datetime, @Today + ' 16:00:00', 120)
/*
print @Today
print @dateFrom
print @dateTo
*/
create table #tempTime
(
UTime datetime,
LTime datetime
)
while @dateFrom <=@DateTo
begin
insert into #tempTime values(@DateFrom,dateadd(mi,5,@DateFrom))
set @Datefrom=dateadd(mi,5,@DateFrom)
end

i want sql query to create a table to store the current date and time (i.e time is between 9:00 to 16:00 current date )

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0