Solved

Sql 2008 server query select all recorders created today varchar date field.

Posted on 2011-03-24
5
301 Views
Last Modified: 2012-05-11
I am trying to retreive all the records from a table comparing against todays date and a varchar that holds the date. The problem is my query never returns any rows. Below a test I ran to determine what is being returned

My query:
SELECT CONVERT(char(8), j.StartTimeStamp, 112) as dt FROM  Jobs AS j INNER JOIN  JobDetails AS jd ON j.OrderId = jd.OrderID where CONVERT(char(8), j.StartTimeStamp, 112) = CONVERT(char(8), '20112403', 112) ;

This:
SELECT CONVERT(char(8), j.StartTimeStamp, 112) as dt
FROM  Jobs AS j
Returns:
3/24/2011

THIS:
SELECT CONVERT(char(8), CURRENT_TIMESTAMP, 112) as dt
Returns:
20110324

Which of course will return false on a compare
Whats the best way to handle this.



0
Comment
Question by:JonMny
[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
  • 3
  • 2
5 Comments
 
LVL 4

Expert Comment

by:jimtpowers
ID: 35209427
Try:

SELECT CONVERT(date, CURRENT_TIMESTAMP, 112) as dt 

Open in new window

0
 
LVL 4

Expert Comment

by:jimtpowers
ID: 35209448
Your query can be:

SELECT CONVERT(DATE, j.StartTimeStamp, 112) as dt
FROM   Jobs AS j
       INNER JOIN  JobDetails AS jd ON j.OrderId = jd.OrderID
WHERE CONVERT(DATE, j.StartTimeStamp, 112) = '2011-03-24';

Open in new window

0
 
LVL 9

Author Comment

by:JonMny
ID: 35209554
This fails with an error: Conversion failed when converting datetime from character string.

SELECT CONVERT(DATE, j.StartTimeStamp, 112) as dt
FROM   Jobs AS j
       INNER JOIN  JobDetails AS jd ON j.OrderId = jd.OrderID
WHERE CONVERT(DATE, j.StartTimeStamp, 112) = '2011-03-24';

0
 
LVL 9

Author Comment

by:JonMny
ID: 35209629

If I try this
SELECT CONVERT(char(8), j.StartTimeStamp, 112) as dt ,CONVERT(char(8), current_timestamp, 112) as dtt FROM  Jobs as j

I get this
3/8/2011      20110324
3/8/2011      20110324
3/8/2011      20110324
3/8/2011      20110324
3/8/2011      20110324
3/8/2011      20110324
3/23/201      20110324
3/24/201      20110324

I'm requesting the same format what am I missing?
0
 
LVL 4

Accepted Solution

by:
jimtpowers earned 500 total points
ID: 35210148
Try this:

SELECT CONVERT(DATE, j.StartTimeStamp, 112) as dt  
FROM   Jobs AS j  
       INNER JOIN  JobDetails AS jd ON j.OrderId = jd.OrderID  
WHERE CONVERT(DATE, j.StartTimeStamp, 112) = CONVERT(DATE, CURRENT_TIMESTAMP);

Open in new window


I'm not sure why you are getting different results asking for the same format. I have had troubles with the formatting as well.
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

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 maintenance plan 8 54
Display field if column exists 7 33
SQL- GROUP BY 4 23
Not listening to where 1 18
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

749 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