Dateformat for sql server in WHERE clause

having
dd-MM-YYYY
MM-dd-YYYY
YYYY-MM-dd

still they won't accept.
Select a.id AS idname, a.userid AS useridname, a.a_categoryid AS acategoryidname, a.code AS acodename, a.pointid AS pointidname, 
            a.title AS titlename, a.briefdesc AS briefdescname, a.contentdesc AS contentdescname, a.filepath AS filepathname, a.pageno AS pagenoname, 
            a.datecreated AS datecreatedname, a.datemodified AS datemodifiedname, ut.name AS utname 
            from article a 
                inner join usertable ut on a.userid = ut.id
                 WHERE a.a_categoryid = 5 AND a.datecreated = '11-13-2010';

Open in new window

LVL 1
doramail05Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
mkobrinConnect With a Mentor Commented:
Perhaps it is because you have time values in the date column. Try using a between clause fro your date filter
Select a.id AS idname, a.userid AS useridname, a.a_categoryid AS acategoryidname, a.code AS acodename, a.pointid AS pointidname, 
            a.title AS titlename, a.briefdesc AS briefdescname, a.contentdesc AS contentdescname, a.filepath AS filepathname, a.pageno AS pagenoname, 
            a.datecreated AS datecreatedname, a.datemodified AS datemodifiedname, ut.name AS utname 
            from article a 
                inner join usertable ut on a.userid = ut.id
                 WHERE a.a_categoryid = 5 AND a.datecreated between '2010-11-13' and '2010-11-14;

Open in new window

0
 
Muhammad Ahmad ImranDatabase DeveloperCommented:
0
 
Muhammad Ahmad ImranDatabase DeveloperCommented:

Select a.id AS idname, a.userid AS useridname, a.a_categoryid AS acategoryidname, a.code AS acodename, a.pointid AS pointidname,
            a.title AS titlename, a.briefdesc AS briefdescname, a.contentdesc AS contentdescname, a.filepath AS filepathname, a.pageno AS pagenoname,
            a.datecreated AS datecreatedname, a.datemodified AS datemodifiedname, ut.name AS utname
            from article a
                inner join usertable ut on a.userid = ut.id
                 WHERE a.a_categoryid = 5 AND convert(datetime, a.datecreated, 101) = '11-13-2010';

Open in new window

0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Muhammad Ahmad ImranDatabase DeveloperCommented:

Select a.id AS idname, a.userid AS useridname, a.a_categoryid AS acategoryidname, a.code AS acodename, a.pointid AS pointidname,
            a.title AS titlename, a.briefdesc AS briefdescname, a.contentdesc AS contentdescname, a.filepath AS filepathname, a.pageno AS pagenoname,
            a.datecreated AS datecreatedname, a.datemodified AS datemodifiedname, ut.name AS utname
            from article a
                inner join usertable ut on a.userid = ut.id
                 WHERE a.a_categoryid = 5 AND convert(datetime, a.datecreated, 110) = '11-13-2010';


another article worth reading

http://www.sqlusa.com/bestpractices/datetimeconversion/

Open in new window

0
 
tzepxCommented:
try without '-', eg: a.datecreated = '20101113' (yyyymmdd)
0
 
doramail05Author Commented:
aiks, none of them ~ o.o
0
 
Muhammad Ahmad ImranDatabase DeveloperCommented:
can you post some results
select datecreated from article where ....


for sample data
0
 
doramail05Author Commented:
this should be alright
Select a.id AS idname, a.userid AS useridname, a.a_categoryid AS acategoryidname, a.code AS acodename, a.pointid AS pointidname, 
            a.title AS titlename, a.briefdesc AS briefdescname, a.contentdesc AS contentdescname, a.filepath AS filepathname, a.pageno AS pagenoname, 
            a.datecreated AS datecreatedname, a.datemodified AS datemodifiedname, ut.name AS utname 
            from article a 
                inner join usertable ut on a.userid = ut.id
                 WHERE a.a_categoryid = 5 AND a.datecreated between '2010-11-13' and DATEADD(DAY,1,'2010-11-13');

Open in new window

0
 
tzepxCommented:
pls try: convert(datetime,convert(char(10),datecreated,101)) = '20101113'

hope you are aware that example date is in the future
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.