Mar 202009
 

I wanted to get all of the rows that were time stamped today from a table that has a datetime column.

Here is the query that I used:

SELECT COUNT(*) AS [Today's File Count] FROM dbo.Envelope WHERE (CONVERT(varchar, entry, 101) = CONVERT(varchar, GETDATE(), 101))

  • Mike Howell

    That’s an interesting way to handle the datetime column to return today’s data. I’ve been using a different method for my queries where I’m interested in data for today, I search for all data greater than CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME). This expression returns todays date along with a time stamp of midnight in the format yyyy-mm-dd 00:00:00.000.

    Your query could be written as;

    SELECT COUNT(*) AS [Today’s File Count]
    FROM dbo.Envelope
    WHERE entry > CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)

    and this too would return all data for today.

  • This would work, except the dates that I am querying contain both the date AND the time, so I want to essentially truncate the time and just give me all of today’s data.

  • MartinJ

    Why not use the built in DATEDIFF function?

    SELECT COUNT(*) AS [Today’s File Count]
    FROM dbo.Envelope
    WHERE DATEDIFF(D, entry, GETDATE()) = 0

  • to essentially truncate the ti