ASP SQL Filter todays date

[ITGURU]

it's been a while since I've done some asp coding but struggling with one part.

I am using dreamweaver to code an ASP page connected to SQL Database.

 

 

Currently on the ASP page I set a cookie with today's date:

 

Response.Cookies ("todaysdate") = Date()

 

 

 

I then filter the database using

 

select * from tablename wherer date=MMColParam

with MMColParam in dreamweaver being:

Request.Cookies("todaysdate")

 

 

My question is how do i filter by today's date in the SQL query without using a cookie.

 

 

I have tried where date = DATE() but that doesnt work.

 

 

Can anyone help please the correct syntax to filter for today's date entries.

thanks.

[pcstru]

It might be handy to know which vendors Database Engine you are using and the version. Assuming it is MsSQL,

 

date = getdate()

 

Will filter to the current date or

 

date = DateFromParts(2017,01,01)

 

allows you to build a date from it's ... err, parts.

 

However, if the fieldtype is DateTime, you may need to range it to cover the 24 hours. Also if the field is not mandatory, then you likely need to explicitly deal with nulls - either to include or exclude them.

Edited March 20, 2017 by pcstru

[ITGURU]

It might be handy to know which vendors Database Engine you are using and the version. Assuming it is MsSQL,

 

date = getdate()

 

Will filter to the current date or

 

date = DateFromParts(2017,01,01)

 

allows you to build a date from it's ... err, parts.

 

However, if the fieldtype is DateTime, you may need to range it to cover the 24 hours. Also if the field is not mandatory, then you likely need to explicitly deal with nulls - either to include or exclude them.

Sorry, yes it's MS SQL.

 

If i use date = getdate() there are no results returned when i browse the page but there are results when use the cookie method.

 

MS SQL database field is dateTime field type but just using the date part

 

 

[ITGURU]

This is what i currently have which works - when i browse the web page i get entries with todays date. Want to remove the need of the Parameter option and directly specify the current date in the SQL query.

[pcstru]

Try with

 

date < getdate()

 

to see if you get any data (assuming that you have dates before today in your data).

 

I guess you might also be executing the ASP via a method that wants to do parameter substitution, which may be objecting to having no parameters.

 

If the time is the problem use DATETIMEFROMPARTS instead and bracket, something like

 

(date >= DATETIMEFROMPARTS(2017,3,20,00,00,00) and date <= DATETIMEFROMPARTS(2017,3,20,23,59,59)) and date is not null.

[ITGURU]

removing the parameter and changing to date < getdate() returns TODAYS entries and previous entries.

 

changing to > getdate() returns tomorrows events.

So why doesnt just =getdate() return just todays?

[pcstru]

removing the parameter and changing to date < getdate() returns TODAYS entries and previous entries.

 

changing to > getdate() returns tomorrows events.

So why doesnt just =getdate() return just todays?

Because it is looking for something happening at exactly midnight. You could cast the datetime to a date or just bracket it as I suggested to ensure you squeeze every last moment from a day :-).

[ITGURU]

I tried the brackets but it said it needed 7 parts.

 

in the SQL Table it is stored as 2017-03-20 00:00:00.000

 

if events are only going to get added each day, could i leave it as

date >= DATEADD(dd, -1, GETDATE())

as there won't be any events for the next date, until they are entered on that date?

As a workaround?

[pcstru]

Sorry,yes, you need milliseconds too, so : DATETIMEFROMPARTS(2017,3,20,0,0,0,0)

 

If you are not using the time why not just change the field type to a date?

[pcstru]

I tried the brackets but it said it needed 7 parts.

 

in the SQL Table it is stored as 2017-03-20 00:00:00.000

 

if events are only going to get added each day, could i leave it as

date >= DATEADD(dd, -1, GETDATE())

as there won't be any events for the next date, until they are entered on that date?

As a workaround?

You could do. IMO it is poor because it replies on an assumption about the data (no future data is entered) but you won't find your door being battered down by the SQL police - so up to you really.

[ITGURU]

Sorry,yes, you need milliseconds too, so : DATETIMEFROMPARTS(2017,3,20,0,0,0,0)

 

If you are not using the time why not just change the field type to a date?

 

It was on Date initially but the same issue occurs it won't read from getDate() either, just blank

[ITGURU]

It was on Date initially but the same issue occurs it won't read from getDate() either, just blank

 

Thank you!!!! Much appreciated, that worked.

[ITGURU]

Today the 'todays entries' list is still showing yesterday date entries.

DATETIMEFROMPARTS(2017,3,20,0,0,0,0)

as i put the above date in it is only using that that.

if i change 20 to 19 it will shows events from the 19th, so it's not changing daily.

 

how do i get it to be the new date each day...

[pcstru]

It is difficult to debug remotely without actual data.

 

Might be worth having a read through of MS documentation on dates :

 

https://msdn.microsoft.com/en-GB/library/ms186724.aspx

 

A possible way to filter to a day might be :

 

Select *

from blah

Where ( day( datefield ) = day( getdate() ) and

month(datefield ) = month( getdate() ) and

year(datefield ) = year( getdate() ) ) and

datefield is not null;

 

You should not need to do that (and it is likely to be horribly inefficient on large datasets) but it may help you debug what is going wrong.