SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Still struggling with a >= to date!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rasta Pickles
Posting Yak Master

United Kingdom
171 Posts

Posted - 03/01/2013 :  07:48:10  Show Profile  Reply with Quote
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
8760 Posts

Posted - 03/01/2013 :  07:52:44  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
171 Posts

Posted - 03/01/2013 :  07:57:50  Show Profile  Reply with Quote
Thanks WebFred but I'm still getting the same number of records.
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 03/01/2013 :  08:00:24  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 03/01/2013 :  08:01:32  Show Profile  Visit webfred's Homepage  Reply with Quote
or:
where left(TransactionDate,4) = '2011'


Too old to Rock'n'Roll too young to die.
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
171 Posts

Posted - 03/01/2013 :  08:10:15  Show Profile  Reply with Quote
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.
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
171 Posts

Posted - 03/01/2013 :  15:48:13  Show Profile  Reply with Quote
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?
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 03/01/2013 :  19:22:30  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
171 Posts

Posted - 03/02/2013 :  02:17:58  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/02/2013 :  02:38:56  Show Profile  Reply with Quote
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/


Edited by - visakh16 on 03/02/2013 02:40:51
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
171 Posts

Posted - 03/02/2013 :  12:56:55  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/02/2013 :  13:40:22  Show Profile  Reply with Quote
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/

Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1644 Posts

Posted - 03/04/2013 :  17:57:51  Show Profile  Reply with Quote
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
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000