Need help to get records of current date?

Question

I am trying to write Stored Procedure for getting records for current date, but I am not able to do so. I have created one for record display with latest first and trying to modify the same.

Here is the code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_SELECTTODAYRECORDS_Test]
AS
BEGIN 
    SELECT col_Source as 'country', 
    col_FirstName +' '+col_LastName as 'name',
    description as 'state' ,
    col_county
    from tbl_Info_Test, tbl_CountySite 
    where col_Pic is not null
    and col_Source = sourcecountry
    and ISDATE(col_BookDate) = 1
    order by CONVERT(datetime, col_BookDate) DESC
END

Why would anyone think that prefixing (practically) every column with `col_` is a good idea? Similarly, `tbl_` and (worst) `sp_`. `sp_` is a prefixed reserved for system stored procedure define by microsoft. But, as a quick quiz, what *else* can appear as a simple name (i.e. not followed by `()`) in a `SELECT` clause other than columns? — Damien_The_Unbeliever, Oct 22 '12 at 13:50
@Damien_The_Unbeliever I agree. But... aliases, @@ functions, $Identity, — podiluska, Oct 22 '12 at 14:21
@podiluska - either the aliases are... aliases for columns, or it's not a simple name (e.g. `()` indicating a function call, or, as you say `@@`, etc) - you can always look at every item in the `SELECT` list and work out what *type* of "thing" it is without needing a `col_` prefix. — Damien_The_Unbeliever, Oct 22 '12 at 14:28

score 1 · Accepted Answer · edited Nov 12 '17 at 12:30

1

If you are trying to get report for today's date, then you can use convert function with GetDate().

If you add following where condition in your SP, it will give records with col_BookDate of today's date.

CONVERT(VARCHAR(10),col_BookDate,101)=CONVERT(VARCHAR(10),GETDATE(),101)

edited Nov 12 '17 at 12:30

halfer

19,824
17
99
186

answered Oct 22 '12 at 13:01

Milind Thakkar

980
2
13
20

VARCHAR conversion is not the most efficient way to do this but it's a valid approach. I don't see a reason for downvoting so I'll upvote. – Yván Ecarri Oct 22 '12 at 13:07

score 1 · Answer 2 · edited May 23 '17 at 10:24

Date fields combine Date and Time information. To select records for current date you have to ommit the time part. One approach is to remove the time part from the field. See the following link:

Most efficient way in SQL Server to get date from date+time?

Other approach is to select all records between current date start (at 00:00) and next day's start:

... WHERE DateField >= @Date AND DateField < DATEADD(d, 1, @Date)

Need help to get records of current date?

2 Answers2