How Do I Select Record From Sql Server Using Dynamic Datetime Pararmeter

Question

1.00/5 (2 votes)

See more:

Hello Everyone,

I am trying to select specific record from sql server based on user date selection, date format for users is in this format:dd/mm/yyyy from Ajax calenda extender, and i use getdate() to store date in the table, i.e. date format in the table is in this format:2015-03-16 08:36:08.330.

I have tried this:

SQL

ALTER PROCEDURE [dbo].[SelectRCOByDate] 
	-- Add the parameters for the stored procedure here
	@Username Varchar (50)=NULL,
	@AttndDate DateTime = NULL
AS
IF @AttndDate is null
SET @AttndDate = CONVERT(VARCHAR, getdate(), 103)
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for procedure here
	SELECT r.Username AS Username, r.surname AS Surname, r.firstname AS Firstname,u.branch AS Branch, r.RTime AS ResumptionTime,r.CTime AS ClosureTime, CONVERT(VARCHAR, r.ATTNDDate, 103) AS ATTNDATE
	FROM RCORegister r
	INNER JOIN UserMgmt u ON r.Username = u.Username
	WHERE  CONVERT(VARCHAR, r.ATTNDDate,103)  = @AttndDate 
	ORDER BY  r.ATTNDDate DESC
 
END

but i am having this error:
Error converting data type varchar to datetime.

Please is there anything i have done wrong? Thanks in advance.

Posted 16-Mar-15 5:12am

Uwakpeter

Add a Solution

Comments

[no name] 16-Mar-15 11:23am

Why don't you store the date as a date?

Sinisa Hajnal 16-Mar-15 11:27am

Why all the converts? If you're passing date into the stored procedure, your table keeps dates as data, the column should be date column and you should be able to compare directly. If you pass varchar from outside and compare with database date column you should have fixed format that you're passing in. Keeping date field as varchar is a) bad design and b) hard to use as you're just realizing. The solution is to check the column for variations and find the format that works. Simple.

Uwakpeter 16-Mar-15 12:53pm

Date column is dateTime type sir and i using GetDate() function to save date, i cant compare it directly because of the millisec and nanosec parts!

Sinisa Hajnal 17-Mar-15 3:11am

Of course you can - see my solution.

Maciej Los 16-Mar-15 11:37am

Do not Repost[^]!!!

3 solutions

Solution 1

Don't convert dates to varchar.

It looks like you're trying to ignore the time part, and retrieve all records for a particular date. If you're using Microsoft SQL Server 2008 or higher, you could change the column and the parameter to the date type[^], which doesn't store the time.

Otherwise, you'll need to look for all dates between the start and end of the specified date:

SQL

IF @AttndDate Is Null SET @AttndDate = GetUtcDate();
SET @AttndDate = DateAdd(dd, 0, DateDiff(dd, 0, @AttndDate));

SELECT 
    r.Username AS Username, 
    r.surname AS Surname, 
    r.firstname AS Firstname,
    u.branch AS Branch, 
    r.RTime AS ResumptionTime,
    r.CTime AS ClosureTime, 
    DateAdd(dd, 0, DateDiff(dd, 0, r.ATTNDDate)) AS ATTNDATE
FROM 
    RCORegister r
    INNER JOIN UserMgmt u ON r.Username = u.Username
WHERE
    r.ATTNDDate >= @AttndDate
And
    r.ATTNDDate < DateAdd(day, 1, @AttndDate)
ORDER BY
    r.ATTNDDate DESC

Posted 16-Mar-15 5:26am

Richard Deeming

Comments

Uwakpeter 16-Mar-15 13:09pm

This only selects records of today, and it doesnt use @AttndDate parameter criteria to select record base on the date user supplies.

Richard Deeming 16-Mar-15 13:11pm

What are you talking about? If you pass the @AttndDate parameter, then that date is used.

Uwakpeter 16-Mar-15 13:28pm

On C#, i am passing user input to @AttndDate parameter

cmd.Parameters.Add("@AttndDate", SqlDbType.DATETIME).Value = txtDate.Text;

Richard Deeming 16-Mar-15 13:37pm

And? If you pass a date in the parameter, that date will be used in the query.

Uwakpeter 16-Mar-15 14:05pm

date that is entered in txtDate is what i am passing to @AttndDate parameter in stored procedure

Richard Deeming 16-Mar-15 14:06pm

And? The date you pass is the date that will be used. The only time it will use the current date is if you don't pass a value for the parameter.

Uwakpeter 16-Mar-15 14:19pm

The only time it will use the current date is when user selects current date from the Ajax Calenda, it will populates all records for today, i want to be able to populate record of yesterday if the selects yesterday's date or and any previous date also.

Richard Deeming 16-Mar-15 14:22pm

I don't know how else to explain this:

Whatever date you pass to the parameter is the date which will be used to select the records.

If you pass yesterday's date, then it will select all records for yesterday.

If you pass a different date, it will select all records for that date.

The date you pass to the parameter is the date which will be used to select the records.

The records will be selected based on the date that you pass to the parameter.

Pass a specific date to the parameter, and you will get back the records for that date.

Uwakpeter 16-Mar-15 14:35pm

this is the date format i am passing:dd/mm/yyyy, and the date in the database is in this format: 2015-03-16 15:33:20.560. i want to be able to convert the saved date to the format that user entered(dd/mm/yyyy) so i can compared them for equality.

i.e. Where savedDate(dd/mm/yyyy) = enteredDate(dd/mm/yyyy). By so doing, i would stripped off the time part and only left with date part, and change the date part to dd/mm/yyyy so i can compare both. Thanks

Richard Deeming 16-Mar-15 14:38pm

NO!

DO NOT convert dates to strings!

Pass and store dates as dates. Anything else is just asking for trouble.

To ignore the time part and just search by date, use the query I posted in my answer three hours ago:


r.ATTNDDate >= @AttndDate
And r.ATTNDDate < DateAdd(day, 1, @AttndDate)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2015-03-16T05:27:00

Solution 2

Since you store the date as a DATETIME value in the DB (or there would be no point in using CONVERT on it), and pass the comparison value as a DATETIME as well, why not just leave it where it is and check the actual value directly?

SQL

...
INNER JOIN UserMgmt u ON r.Username = u.Username
WHERE  r.ATTNDDate = @AttndDate
ORDER BY  r.ATTNDDate DESC

Posted 16-Mar-15 5:27am

OriginalGriff

Comments

Uwakpeter 16-Mar-15 12:47pm

i am going to accept date from users in this format: dd/mm/yyyy
so if i use WHERE r.ATTNDDate = @AttndDate, that wont work!

OriginalGriff 16-Mar-15 12:55pm

You are passing the date from the user though as a DATETIME parameter:

@AttndDate DateTime = NULL

And DATETIME values don't have a format (that's one of the really good things about them) - they are stored as a number of milliseconds since a specific point in time, so it doesn't matter what format the user enters the date in - once it's a DATETIME it has lost all formatting and will compare against any other DATETIME value correctly.

Uwakpeter 16-Mar-15 12:59pm

yes, i am passing the date from the user though as a DATETIME parameter:

@AttndDate DateTime = NULL

OriginalGriff 16-Mar-15 13:12pm

So your two values are both DATETIME and will compare natively regardless of the format they were originally entered in.

The only thing you might want to consider (if your original INSERT was a GETDATE() call) is that a DATETIME stores the time to millisecond level - so it could be worth CASTing the two values to float and using FLOOR to remove the time when you do equality checks:

INNER JOIN UserMgmt u ON r.Username = u.Username
WHERE FLOOR((CAST(r.ATTNDDate AS FLOAT)) = FLOOR((CAST(@AttndDate AS FLOAT))
ORDER BY r.ATTNDDate DESC

Since SQL DATETIME is a float value, and the time is held in the fractional part, this compares just the Date parts very, very quickly.

Uwakpeter 16-Mar-15 13:25pm

have tried this, this is the error message:
An expression of non-bolean type specified in a context where a condition is expected!

OriginalGriff 16-Mar-15 13:43pm

Oops! I put to many open brackets in! :blush:

INNER JOIN UserMgmt u ON r.Username = u.Username
WHERE FLOOR(CAST(r.ATTNDDate AS FLOAT)) = FLOOR(CAST(@AttndDate AS FLOAT))
ORDER BY r.ATTNDDate DESC

Uwakpeter 16-Mar-15 14:19pm

On C#, i am passing user input to @AttndDate parameter

cmd.Parameters.Add("@AttndDate", SqlDbType.DATETIME).Value = txtDate.Text;

The only time it will use the current date is when user selects current date from the Ajax Calenda, it will populates all records for today, i want to be able to populate record of yesterday if the selects yesterday's date or and any previous date also.

OriginalGriff 16-Mar-15 15:00pm

Then use "greater than or equal to" instead of equals!

Uwakpeter 16-Mar-15 14:01pm

This only selects today's records, and it doesnt use the parameter, if i yesterday's date to get yesterday record, it still selects today's record. Any further assistance would be appreciated. thanks

Sinisa Hajnal · Accepted Answer · 2015-03-16T21:19:00

Solution 3

You should never involve strings into date comparisons. It is slow, easy to get wrong and hard to debug.

The following will all give you options for your select:

DATEADD(dd, DATEDIFF(dd, 0, r.ATTNDDate), 0) will give you date only
as will
CONVERT(DATE, r.ATTNDDate) - this assumes SQL Server 2008 or higher

You comparison could be:

DATEADD(dd, DATEDIFF(dd, 0, r.ATTNDDate), 0) = @AttNDDate if your variable is "pure" date (that is time component is zero)

Change the comparison operator as needed...

If, in your table, you have whole range of times then you could do
r.ATTNDDate BETWEEN @AttNDDate AND DateAdd(d, 1, @AttNDDate )

You could simplify this even further if you don't need time component in the database. Just change your insert statement to cast GetDate() as above to get only date component (and you'll have to run an update on the existing data of course). This way you can do plain and simple comparison
r.ATTNDDate = @ATTNDDate

I hope this helps. Good luck.

Posted 16-Mar-15 21:19pm

Sinisa Hajnal

Comments

Uwakpeter 17-Mar-15 4:02am

Thanks, Please should i change datetime type in the date column to date type? so it stores only date and time part will be zero?

Sinisa Hajnal 17-Mar-15 4:35am

That would be ideal. But if you don't want (or cannot) change nad since you already have datetime you could only update it (and take care what get into it) :)

Uwakpeter 17-Mar-15 6:18am

Thank you sir, i have changed the dateTime type to Date in the date column, i now change the stored procedure to this:

ALTER PROCEDURE [dbo].[SelectRCOByDate]
-- Add the parameters for the stored procedure here
@Username Varchar (50)=NULL,
@AttndDate Date =NULL
AS
IF @AttndDate is null
SET @AttndDate = getdate()
BEGIN

SET NOCOUNT ON;
--SET DATEFORMAT dmy
-- Insert statements for procedure here
SELECT r.Username AS Username,
r.surname AS Surname,
r.firstname AS Firstname,
u.branch AS Branch,
r.RTime AS ResumptionTime,
r.CTime AS ClosureTime,
r.ATTNDDate AS ATTNDATE
FROM RCORegister r
INNER JOIN UserMgmt u ON r.Username = u.Username
WHERE r.ATTNDDate = @AttndDate
ORDER BY r.ATTNDDate DESC
END

This only returns current date records, and it doesnt check for @AttndDate parameter, i.e. i can run it without the parameter and it will still returns records. Please how can i handle this?

Sinisa Hajnal 17-Mar-15 7:00am

It does wor @AttndDate parameter...its only that you've set it to current date.
If you pass it some other value it will take rows with given date.

It returns records because you supplied default values (NULL) to both parameters...if you don't want the SP to run without parameters remove = NULL from parameter declaration.

Uwakpeter 17-Mar-15 11:42am

Thanks you sir, when i remove this lines:
IF @AttndDate is null
SET @AttndDate = getdate()

and declare:@AttndDate Date (without assigning null to it) it reports error!

Sinisa Hajnal 18-Mar-15 3:07am

Yes, obviously...If you don't pass the parameter and it you don't have default defined it will report an error.

Sinisa Hajnal 17-Mar-15 7:01am

Your SP does not use @Username parameter...which you should do with
r.Username = ISNULL(@Username, r.Username) - that is if you pass NULL value return all users

Uwakpeter 17-Mar-15 11:53am

i want to use username and date as search criteria, though i have included the username in the where clause yet!

Sinisa Hajnal 18-Mar-15 3:08am

That's OK, resolve the date problem first. When you call SP you have to include parameters, otherwise SP will use defaults.