0

I have a field titled DTUpdated and it is a timestamp field.

I am trying to select a count of the dates in the rows that are xx old. i.e. 0-3 months, 3-6 months etc.

I am not at the writing date selection criteria for the selection yet as i am stuck getting the first part of the query to simply count the occurrences of each day in a results pane.

The query i have so far is as follows but it is not grouping the data by the days as returned.

SELECT CONVERT(VARCHAR(10),[SCAData].[dbo].[Camera].[DTUpdated],105) AS Date_Updated,
COUNT(DTUpdated) AS No_of_Rows 
FROM [SCAData].[dbo].[Camera]
GROUP BY DTUpdated  
ORDER BY DTUpdated; 

Result:

Date_Updated    No_of_Rows
03-10-2006      1
03-10-2006      1
05-10-2006      1
05-10-2006      1
05-10-2006      1
05-10-2006      1
05-10-2006      1
05-10-2006      1
05-10-2006      1
06-10-2006      1
06-10-2006      1
06-10-2006      1
06-10-2006      1
06-10-2006      1

Can someone please help or also suggest how to then get these back based on date selections of last 3 months, 3-6 months?

Many thanks in advance.

10
  • what database are you using? SQL Server? postgres? Oracle? mysql? other? and what version Jan 7, 2021 at 16:12
  • I am using SQL Management Studio.
    – Brynster
    Jan 7, 2021 at 16:24
  • I have since managed to get this far to select count by day and country (constabulary)
    – Brynster
    Jan 7, 2021 at 16:25
  • 1
    Hi. This is on MS SQL Server and i have just checked the properties of the column and it is in fact a datetime field.
    – Brynster
    Jan 8, 2021 at 17:20
  • 1
    The version is as follows: Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64) Apr 29 2016 23:23:58 Copyright (c) Microsoft Corporation Web Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
    – Brynster
    Jan 8, 2021 at 17:20

1 Answer 1

0

Since the column type is a datetime and this is SQL Server 2016, converting it to a date is easy :

SELECT CONVERT(Date,[SCAData].[dbo].[Camera].[DTUpdated]) AS Date_Updated,
COUNT(DTUpdated) AS No_of_Rows 
FROM [SCAData].[dbo].[Camera]
GROUP BY CONVERT(Date,[SCAData].[dbo].[Camera].[DTUpdated])  
ORDER BY 1; 

If you really want to do it via an VARCHAR field, just do it like that :

SELECT CONVERT(VARCHAR(10),[SCAData].[dbo].[Camera].[DTUpdated],105) AS Date_Updated,
COUNT(DTUpdated) AS No_of_Rows 
FROM [SCAData].[dbo].[Camera]
GROUP BY CONVERT(VARCHAR(10),[SCAData].[dbo].[Camera].[DTUpdated],105)  
ORDER BY 1; 
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.