Solved

Dateformat for sql server in WHERE clause

Posted on 2010-11-11
9
272 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Expert Comment

by:tzepx
ID: 34110136
try without '-', eg: a.datecreated = '20101113' (yyyymmdd)
0
 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

691 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