Get Hourly Created records count for a day from a SQL Table

SELECT STUFF(RIGHT(CONVERT(VARCHAR,Createddate,0),7),3,3,' ') + ' - ' +
STUFF(RIGHT(CONVERT(VARCHAR,DATEADD(HOUR,1,Createddate),0),7),3,3,' ') AS HOUR, COUNT(*) AS COUNT
FROM tableName
WHERE Createddate BETWEEN CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE()))) AND CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE())) + 1)
GROUP BY STUFF(RIGHT(CONVERT(VARCHAR,Createddate,0),7),3,3,' ') + ' - ' +
STUFF(RIGHT(CONVERT(VARCHAR,DATEADD(HOUR,1,Createddate),0),7),3,3,' ')
ORDER BY 1

Comments

Post a Comment

Popular posts from this blog

How to Convert Word Document to PDF using C#

  Document wordDocument = null;             string Sourcepath = ConfigurationSettings.AppSettings["SourcePath"];             string DestinationPath = ConfigurationSettings.AppSettings["DestinationPath"];             DirectoryInfo dirInfo = new DirectoryInfo(Sourcepath);             FileInfo[] wordFiles = dirInfo.GetFiles("*.doc");             foreach (FileInfo wordFile in wordFiles)             {                 object paramSourceDocPath = (Object)wordFile.FullName;                 //(Object)wordFile.Name                 string paramExportFilePath = paramSourceDocPath.ToString().Replace(".doc", ".pdf"); //  string paramExportFilePath = DestinationPath + wordFile.Name.ToString().Replace(".doc", ".xps");                 if (!File.Exists(paramExportFilePath.ToString()))                 {                     Application wordApplication = new Application();                        object paramMissing = Type.M
Read more

Loop Through Dates in SQL

DECLARE @StartDate DATE = '2019-12-17'; DECLARE @EndDate DATE = '2021-05-01' declare @Edate date declare @sdate date set @Edate = DATEADD(day, 30, @StartDate) set @sdate = @StartDate WHILE (@Edate <= @EndDate) BEGIN print 'start' print @sdate print @Edate set @sdate = @Edate set @Edate = DATEADD(day, 30, @sdate); print @sdate print @Edate print 'end' END
Read more