?
Solved

Dateformat for sql server in WHERE clause

Posted on 2010-11-11
9
Medium Priority
?
276 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Expert Comment

by:tzepx
ID: 34110136
try without '-', eg: a.datecreated = '20101113' (yyyymmdd)
0
 
LVL 7

Accepted Solution

by:
mkobrin earned 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

762 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