SQLTeam.com | Weblogs | Forums

SQL dates


#1

Hi,

Simple qu hopefully.

I am trying to run a query (scheduled) to group the date field by day (ignoring time). How can Id do this so the report can be run every day?

select top 1000 CONVERT (date, SYSDATETIME()) TodaysDate,
count(fieldname) as validateddate FROM databasename
WHERE Convert(DATE, fieldname) = '2018-08-01'

I have this for one day.. but I don't want to have to manually change the date


#2

Use a SQL Server Agent Job or a Windows Task to run a batch.


#3

What type of field is fieldname?


#4

Something like this:

SELECT TOP (1000) CAST(SYSDATETIME() AS date) TodaysDate,
    COUNT(fieldname) as validateddate 
FROM tablename
WHERE CAST(fieldname AS date) = CAST(SYSDATETIME() AS date)
GROUP BY CAST(SYSDATETIME() AS date)