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
Solved

Dateformat for sql server in WHERE clause

Posted on 2010-11-11
9
265 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help  needed 3 35
Linked Server Issue with SQL2012 3 28
Stored Proc - Rewrite 42 59
Index and Stats Management-Specific tables 8 22
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

856 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