Solved

Dateformat for sql server in WHERE clause

Posted on 2010-11-11
9
257 Views
Last Modified: 2012-06-21
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

0
Comment
Question by:doramail05
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 14

Expert Comment

by:leoahmad
ID: 34110106
0
 
LVL 14

Expert Comment

by:leoahmad
ID: 34110121

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
 
LVL 14

Expert Comment

by:leoahmad
ID: 34110127

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
 

Expert Comment

by:tzepx
ID: 34110136
try without '-', eg: a.datecreated = '20101113' (yyyymmdd)
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 7

Accepted Solution

by:
mkobrin earned 500 total points
ID: 34110178
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
 
LVL 1

Author Comment

by:doramail05
ID: 34110201
aiks, none of them ~ o.o
0
 
LVL 14

Expert Comment

by:leoahmad
ID: 34110213
can you post some results
select datecreated from article where ....


for sample data
0
 
LVL 1

Author Comment

by:doramail05
ID: 34110222
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
 

Expert Comment

by:tzepx
ID: 34110245
pls try: convert(datetime,convert(char(10),datecreated,101)) = '20101113'

hope you are aware that example date is in the future
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now