compare date column field with variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

compare date column field with variable

Author

Topic

bbxrider
Starting Member

37 Posts

Posted - 2010-04-28 : 11:49:01

This statement is not working

strSQL = " SELECT * FROM orientationData where orientationDate > " & 'postDate'

postDate is set up with
postDate = "04/28/2010" (this eventually will be a date from a web form)
postDate = CDate(postDate)

since I can't change how the db date field is used in the select, I must need to do something to the variable? or change the syntax in the select statement?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-28 : 11:51:50

is postdate of type datetime

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-28 : 12:21:18

If the postDate is of datetime datatype then you can directly substitue the variable.

In case it is of varchar type then you need to convert it to datetime.

Example:
select Convert(datetime,'04/28/2010',101)

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

bbxrider
Starting Member

37 Posts

Posted - 2010-04-28 : 12:33:06

this is traditional asp, not asp.net
originally I created postDate like this
dim postDate
postDate = "04/28/2010"
postDate= CDate(postDate)
and with this postDate works in the asp dateDiff function

since the post asking if postDate was a datetime variable I tried this but it also did not work
dim postDate
dim dateWork
postDate = "04/28/2010"
dateWork = CDate(postDate)
strSQL = " SELECT * FROM orientationData where orientationDate > " & dateWork

btw, the result recordset includes all the recs, no matter what the date is

bbxrider
Starting Member

37 Posts

Posted - 2010-04-29 : 02:36:00

finally found the answer, since this is an access db, literal dates need to be surrounded by ##, similar to asp date field, eg,
dim aDate
aDate =#04/28/2010# 'eliminates need to do a cdate function
and similar to sql server using ''

so the sql string is constructed thus
" SELECT * FROM orientationData where orientationDate > #" & postDate & "# and EmailSentCount < targetAttendeeCount"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources