Only showing previous day's data based on varchar

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Only showing previous day's data based on varchar

Author

Topic

meef
Posting Yak Master

113 Posts

Posted - 2014-12-02 : 16:05:47

I'm trying to only show yesterday's data based on a date that is stored as a varchar. I converted it to smalldatetime but I'm getting an error that says "Conversion failed when converting date and/or time from character string." I don't know how I have to alter the conversion.

Here is my code:

CONVERT(varchar(20), CONVERT(date, CONVERT(varchar(8), date_added), 112),110) = dateadd(day,datediff(day,1,GETDATE()),0)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-02 : 16:08:13

Sounds like you've got bad data: select * from yourtable where ISDATE(yourdatecolumn) <> 1

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

meef
Posting Yak Master

113 Posts

Posted - 2014-12-02 : 16:14:59

I'm sure that's a possibility given how things have been going so far. Running your query above though returned no results.

meef
Posting Yak Master

113 Posts

Posted - 2014-12-03 : 16:11:42

Ok, can anyone tell me why this is not filtering on the date_added column?

SELECT c.bol_number, a.scac, a.pro, CONVERT(varchar(20), CONVERT(date, CONVERT(varchar(8), a.delivered_date), 112),110)as date_delivered, convert
(time(0),LEFT(b.status_time,2) +':'+ RIGHT(b.status_time,2)) as status_time, date_added
 FROM tbl_214_datatable AS a 
inner JOIN (select bol_id, 
                           max(status_date) as status_date, 
                           max(status_time) as status_Time 
            from tbl_214_status
            where status_code = 'D1'
            --and date_added ='20141201'
            group by bol_id) AS b 
ON b.bol_id = a.bol_id
inner join tbl_bol as c on b.bol_id=c.bol_id 
WHERE c.dba_id = '09acea7e-4cb5-4dba-a829-d1f8c411909b' and YEAR(a.delivered_date) = YEAR(GETDATE())
ORDER BY date_added  DESC

If I put that filter in place I get no results. If I run without, there are records with that date in them.

If I used date_added between '20141101' and '20141130' I get dates ranging from 11/27/14 all the way down to dates with 2012 in them.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-03 : 16:14:24

what is the datatype of the date_added column?

meef
Posting Yak Master

113 Posts

Posted - 2014-12-03 : 16:20:45

smalldatetime

Also, If I used date_added between '20141101' and '20141130' I get dates ranging from 11/27/14 all the way down to dates with 2012 in them.

I've also tried using exactly the data that is in the table, which is 2014-12-02 00:00:00 and it still shows nothing.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-03 : 16:35:58

and date_added >= '20141201' and date_added < '20141202'

Run just the derived table, aliased as b. If you get data back from that but don't get data back from the larger query, then the issue is likely with the join or the larger query (not the derived table portion).

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

meef
Posting Yak Master

113 Posts

Posted - 2014-12-03 : 16:47:51

If I just pick that table that where statement works. I have no clue where it went wrong with the original or how to fix it, I'm going to smash my head through a wall.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-03 : 16:53:12

I'm thinking the problem is with the table aliased as c. Remove that join and everything that references that table. Does the query work okay with just a and b?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

meef
Posting Yak Master

113 Posts

Posted - 2014-12-03 : 16:58:26

I think you're onto something... I just noticed there is a date_added in the c.tbl_bol table also. I think it's looking at that one instead of the date_added that's in the b.tbl_214_status. I'll have to try to figure out how to get it looking at the right one.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-03 : 17:01:18

It doesn't matter if there's a date column in there. What matters is how the column is aliased or if it's inside the derived table. If two columns are named the same in the same scope, you have to alias them or SQL will throw a syntax error. The date_added column in the derived table is not in the same scope as the c table.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources