Solved

SQL Server DatePart HOUR

Posted on 2016-08-11
6
93 Views
Last Modified: 2016-08-11
Does my select below differentiate on the 24 hour clock?

        DECLARE @Hours INT
        SET @Hours = DATEPART(HOUR, GETDATE())
0
Comment
Question by:lrbrister
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 400 total points
ID: 41752400
Yes, as internally dates are stored as HH:MM:DD with a 24-hour day.
SELECT DATEPART(HOUR, GETDATE())      -- 11 when run now in CST
SELECT DATEPART(HOUR, GETDATE() + .5) -- 23 when run now in CST

Open in new window

Can you give us more details as to what your requirements are?
1
 

Author Comment

by:lrbrister
ID: 41752405
That was it... I was just verifying that this would work...
Maybe the every two hours part is clunky?

DECLARE @LastEntry DATETIME
DECLARE @Hours INT
SET @Hours = DATEPART(HOUR, GETDATE())
SET @LastEntry = ( SELECT   MAX(ISNULL(DateAdded, GETDATE()))
                    FROM     dbo.SendGridAllocations
                    )

IF (@Hours - DATEPART(HOUR, @LastEntry) > 0 AND @Hours IN (2,4,6,8,10,12,14,16,18,20,22))
    BEGIN

Open in new window

0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41752415
Explain for us at Barney-level what you are trying to pull off, as I'm not getting what you are asking us.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:lrbrister
ID: 41752434
Well...
I am getting usage stats from an api in my own .Net app
Those stats come in on the same task that runs every 10 minutes for other "methods"
I want to store the usage stats every two hours instead of every 10 minutes.

So... without touching the .Net application that ALREADY is inserting the data
I want to modify the stored procedure to do it only every 2 hours
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 total points
ID: 41752453
You can shorten the code.  Btw, it's somewhat iffy to assume that the LastEntry day will be today, but presumably you know that it "can't" fail and will always be the current date:

IF (@Hours - DATEPART(HOUR, @LastEntry) > 0 AND @Hours % 2 = 0
0
 

Author Closing Comment

by:lrbrister
ID: 41752494
Perfect... thanks
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question