passing a date to stored procedure need help with formating

[DougP]

I have a stored procedure and when I try and run it it return no results.
this is a sample of a data in the date column:
2012-05-19 07:35:00
when I test it what do I need to put in the following:
execute GetAttendTotalsByDay 3, '05-19-2012' -- ???

Also I'm calling this from ASP.NET 4.0 VB code WEB page.
So I would just like to pass the date to the procedure in a format like dd-mm-yyyy
and have the stored procedure interrogate that and then return values.

Create Procedure GetAttendTotalsByDay
	(
	@grade nvarchar(1),
	@Date as smalldatetime
	)
AS
	Declare @TotalTardy int
	set @TotalTardy  = (Select  Count(Present) as  TotalTardy
		from Present 
		where grade = @grade and [Present] = 'T' and AttendDate = @Date)
	Select @TotalTardy  
return

DougP

 

[Olaf Doschke]

Your first parameter is nvarchar(1), so don't pass in 3, but '3'.

Your second parameter is a smalldatetime, this is a type specific to sql-server, asp.net is VB as you say. You will either need to change the parameter to accept a string to pass in a string, or pass in a datetime value. SQL Server will do implicit conversions, I think, so you could try 'YYYYMMDD' as a canonical type, but not 'mm-ddd-yyyy'.

Bye, Olaf.

 

[DougP]

I want to know what to type in to make it work?
if I type in '05-19-2012' it does not recognize it as a date and returns no results.

'05-19-2012%' this either since the field contains this teh date and thetime.
2012-05-19 07:35:00
how do I ask it for a date and ignore the time?
I need to use convert or case or???

DougP

 

[Olaf Doschke]

Why not simply try what I suggested? YYYYMMDD, so in your case '20120519'. REally without any seperator.

Time will then be taken as midnight, if not specified. So either AttendDate also has midnight as the time portion, or is a date field (which exists since SQL2008).
You're free to do what you need. So if you don't want to specify time, then make it a date parameter instead of smalldatetime.

Bye, Olaf.

 

[DougP]

it also returns nothing, I tried that first.
I need time too, and thought I could put them both in one.
So if I want to extract either just a date or just time I cannot I need to put them both in separate fields?


DougP

 

[gmmastros]

When you compare a date that has a time to a date without a time, you will never get a match. If you want any match for a given day regardless of time, you should do it as two separate conditions.

DateCol >= '20120519' and DateCol < '20120520'

I'm typing this on my phone. Otherwise I would explain this better and provide a working example.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Olaf Doschke]

>it also returns nothing, I tried that first.
Sorry, I can't read your mind, I don't know that you also tried. I have to assume you were not taking advices. And how do you think this makes me feel, independant of if my advice failed or not. I know advices can fail, if I don't know the full situation. I don't know the field type of AttenDate, in this case.

Besides that, George is right: You either need two conditions with > and < or a BETWEEN clause to cover the interval of the full date from midnight to midnight. Datetimes of a certain date are between midnight (start of that date) and midnight (end of the date and start of the next date)

So you can't put that in one condition, AttendDate is a datetime. A comparison of any field will always compare the full field content. If you sepcify a datetime without a time portion to a datetime field, this doesn't make sql server just compare the date portion. If you compare an integer field to 1000 you also don't expect to get records into the query result, if the integer is between 1000 and 1999, do you? What kind of weird logic are you expecting? No software is intelligable, it always does, what it's told to do.

But your select in conjunction with my advice would work, if AttendDate would be a date field.

You neither tell the AttendDate type nor what you really can change. I said "Time will then be taken as midnight, if not specified. So either AttendDate also has midnight as the time portion, or is a date field." Do you now get, what that means?

I also don't know, if you only can influence the VB ASP.NET code, or also can change that stored proc. I said "You're free to do what you need." But actually I don't know how free you are to change the code on both sides. If yoiu have that freedom, then why do you burden yourself with a problem you simply can choose not to have? If you want to compare to a date, then make it a date field. If a AttendDate being a datetime is of value in other situations and queries, then change the query to AttendDate >=@date and AttendDate<dateadd(d,1,@Date) and pass in the start date(time) 'YYYYMMDD'

If you can't change the stored proc you can only query an exact datetime, then make your own query string and pass it to sql server and don't use such a specific stored proc.

Bye, Olaf.

 

[DougP]

this is what I was looking for

Select convert(varchar, AttendDate, 110) as TheDate from present
group by convert(varchar, AttendDate, 110)
order by convert(varchar, AttendDate, 110) DESC

DougP

 

[Olaf Doschke]

OK, Doug

just my 2¢: It's a bad habit to fomat dates within SQL. The formatting is a job for a client side tool. In this case you waon't order dates of several years the correct way.

Since SQL2008 you could do

Select cast(AttendDate as date) as TheDate
from present
group by cast(AttendDate as date)
order by AttendDate Desc

And in older versions, you can strip off the time portion of a datetime via DATEADD(dd, 0, DATEDIFF(dd, 0, AttendDate)). It was unclear the AttenDate had a time portion. It's quite normal to store dates as datetime with the time portion set as midnight, which is equal to a date only type.

But there is no need to change to a date string format to sort, also no need to group by a string format of the date, if you want to group by the date portion. Besides I don't get how this answer relates to your original question. But again, that's just my 2¢.

Bye, Olaf.

 

[DougP]

I find it interesting that people think "date" is a valid SQL data type.
this is SQL sever 2008.
Select cast(AttendDate as date[color])

Error
Msg 243, Level 16, State 1, Line 1
Type date is not a defined system type.
Msg 243, Level 16, State 1, Line 1
Type date is not a defined system type.

as far as the "group by" I am looking a table with hundreds of records and I just want the unique dates. I also want to only show valid dates with which the user can pick from. So naturally I am looking at the data rather than just letting them choose from any 'ol date. I also wanted to show the most recent data at the top thus the reason for the sort DESC. since they would be more concerned with info. just a few days old rather than 8 months ago.

the first few lines of my fits post had this

this is a sample of a data in the date column:
2012-05-19 07:35:00

DougP

 

[gmmastros]

Doug,

Can you please run this query and post the results?

select compatibility_level 
from   sys.databases 
where  database_id = db_id()

Please make sure you are connected to the correct database before running the query.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Olaf Doschke]

George may reveal the reason your instance still denies this type's existance, because compatibility level is set to an earlier version. But even if so, I also gave you an alternate expression to stripe off the time. If you're satisfied with your own solution, that's also fine.

Date and Time Data Types and Functions: [link msdn.microsoft.com/en-us/library/ms186724.aspx]

http://msdn.microsoft.com/en-us/library/ms186724.aspx

[/url]
Date Type: [link msdn.microsoft.com/en-us/library/bb630352.aspx]

http://msdn.microsoft.com/en-us/library/bb630352.aspx

[/url]

It's getting 4 years old. Not talking about other databases than SQL Server.

Bye, Olaf.