Solved

Dateformat for sql server in WHERE clause

Posted on 2010-11-11
9
259 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2008 R2 - Updating Table/Fields Documentation 3 72
Sql query 34 35
sql query help 4 45
MS SQL - Rotating Values in SQL 9 53
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…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

895 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

11 Experts available now in Live!

Get 1:1 Help Now