| Author |
 Topic  |
|
|
Rasta Pickles
Posting Yak Master
United Kingdom
133 Posts |
 Posted - 03/01/2013 : 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
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 03/01/2013 : 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
United Kingdom
133 Posts |
Posted - 03/01/2013 : 07:57:50
|
| Thanks WebFred but I'm still getting the same number of records. |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 03/01/2013 : 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
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 03/01/2013 : 08:01:32
|
or:
where left(TransactionDate,4) = '2011'
Too old to Rock'n'Roll too young to die. |
|
|
|
Rasta Pickles
Posting Yak Master
United Kingdom
133 Posts |
Posted - 03/01/2013 : 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
United Kingdom
133 Posts |
Posted - 03/01/2013 : 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
Flowing Fount of Yak Knowledge
Germany
8515 Posts |
Posted - 03/01/2013 : 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
United Kingdom
133 Posts |
Posted - 03/02/2013 : 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
India
47166 Posts |
|
|
Rasta Pickles
Posting Yak Master
United Kingdom
133 Posts |
Posted - 03/02/2013 : 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
India
47166 Posts |
Posted - 03/02/2013 : 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
Flowing Fount of Yak Knowledge
USA
1430 Posts |
Posted - 03/04/2013 : 17:57:51
|
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 HierarchySQL seems to recognize which one is bigger.
=================================================
There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber |
|
|
| |
Topic |
|
Still struggling with a >= to date!
