Still struggling with a >= to date!

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

Still struggling with a >= to date!

Author

Topic

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-03-01 : 07:48:10

Sorry for this, I'm still struggling with dates.

I'm retrieving data from a view where TransactionDate is in the format yyyymmdd (i.e 20130301).

My test code is

 WHERE TransactionDate >= '20110101'

which retrieves everything from 2012 and 2013 but nothing for 2011 whereas there should be transactions returned.

I tried

 WHERE CAST (TransactionDate AS VARCHAR(8)) >= '20110101'

but it makes no difference.

TransactionDate is of type CHAR(8).

What am I doing wrong?

Thanks as always.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-01 : 07:52:44

Try
where convert(date,TransactionDate) >= '20110101'

or if the server version can't work with date

where convert(datetime,TransactionDate) >= '20110101'

Too old to Rock'n'Roll too young to die.

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-03-01 : 07:57:50

Thanks WebFred but I'm still getting the same number of records.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-01 : 08:00:24

Then maybe there are no rows with date from year 2011...

Check it:
select * from table where year(TransactionDate) = 2011

Too old to Rock'n'Roll too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-01 : 08:01:32

or:
where left(TransactionDate,4) = '2011'

Too old to Rock'n'Roll too young to die.

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-03-01 : 08:10:15

Thanks Fred, what a muppet I am......yesterday there were records in there from 2011......it appears they've modified the view overnight and only 2012 records are in there!

So your code was fine, many thanks.

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-03-01 : 15:48:13

Just as an added thought - in my case, the original syntax was fine because there was nothing in the view relating to 2011?

So I didn't need to add anything?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-01 : 19:22:30

In your table - what is the data type of TransactionDate? That's the point to answer this question.

Too old to Rock'n'Roll too young to die.

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-03-02 : 02:17:58

TransactionDate is of type CHAR(8).

If you see some of my other posts on this forum, I've never quite got my head around how SQL treats dates :-(

Maybe it needs a sticky post?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-02 : 02:38:56

quote:
Originally posted by Rasta Pickles

TransactionDate is of type CHAR(8).

If you see some of my other posts on this forum, I've never quite got my head around how SQL treats dates :-(

Maybe it needs a sticky post?

Then you've only yourself (or whoever who did it) to blame

If it store dates then datatype should be datetime
there's a definite purpose behind having different datetime types so you need to use proper datatype to take advantage of functions sql provides for manipulations etc

if you want to understand how sql treats dates have a look at below threads

http://visakhm.blogspot.in/2012/07/generate-datetime-values-from-integers.html

http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2013-03-02 : 12:56:55

But your second link (excellent blog by the way) seems to encourage the ISO format, which is what we're talking about here.

Isn't it?

In simple terms, how do I get SQL to recognise that 20130102 is greater than 20130101?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-02 : 13:40:22

quote:
Originally posted by Rasta Pickles

But your second link (excellent blog by the way) seems to encourage the ISO format, which is what we're talking about here.

Isn't it?

In simple terms, how do I get SQL to recognise that 20130102 is greater than 20130101?

by storing it in a datetime field and sorting on it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-03-04 : 17:57:51

[CODE]declare
@TransactionString1 char(8) = '20130102',
@TransactionString2 char(8) = '20130101'

select
case
when @TransactionString1 > @TransactionString2 then 'Greater'
when @TransactionString1 = @TransactionString2 then 'Equal'
when @TransactionString1 < @TransactionString2 then 'Less'
else 'Warning: Laws of universe have dissolved...'
end Hierarchy[/CODE]SQL seems to recognize which one is bigger.

=================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources