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
|
Trywhere convert(date,TransactionDate) >= '20110101'or if the server version can't work with datewhere 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 blameIf it store dates then datatype should be datetimethere'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 etcif you want to understand how sql treats dates have a look at below threadshttp://visakhm.blogspot.in/2012/07/generate-datetime-values-from-integers.htmlhttp://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.htmlhttp://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 MVPhttp://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 |
|
|
|