Date Range - SQL Server Forums

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

Date Range

Author

Topic

JohnGault777
Starting Member

31 Posts

Posted - 2011-02-11 : 12:26:04

I simply need to return the a count of the items between two dates. I'm new to SQL Server, so this is probably an extremely basic request. How do I format the date to say count the number of particular items between two dates? I put the code below, but I just need the correct between date format.

The code below returns nothing but I know there is something. Please help.

select count(distinct address1), fname from insured i
INNER JOIN policy p
ON i.insuredid = p.insuredid
inner join users u
on p.applicationusersid = u.usersid
where policytype = 1
and status = 99
and i.adddate >= DATENAME(day, '10:07:01.123')
AND i.adddate < DATENAME(day, '10:09:30.123')
group by fname

JG777

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-02-11 : 12:44:44

If i.adddate is date datatype, you can use
i.adddate >= '20100701'
and i.adddate <='20100930' which will give you everything between July 01, 2010 and Sep 30,2010

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-02-12 : 03:22:22

is adddate date field? do you specifically have to look for time limits also in your filter condition?

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-12 : 04:06:31

DATENAME(day, '10:07:01.123')

will return 1, because there is no date information, only timeinformation, so the function will return 1 for default 19000101.

>= 1 AND < 1 will returns those record for adddate which are EXACTLY 19000101 00:00:00.000

N 56°04'39.26"
E 12°55'05.63"

chris_n_osborne
Starting Member

34 Posts

Posted - 2011-02-12 : 12:57:44

quote:
Originally posted by Peso

>= 1 AND < 1 will returns those record for adddate which are EXACTLY 19000101 00:00:00.000

I was thinking that >=1 and < 1 would always return FALSE. Wouldn't that return no results?


Begin
  Declare @MyCondition as INTEGER
  Set @MyCondition = 1

  If @MyCondition >= 1 AND @MyCondition < 1
    Print 'True on = 1'
  Else
    Print 'False on = 1'
    
  Set @MyCondition = 0
  If @MyCondition >= 1 AND @MyCondition < 1
    Print 'True on < 1'
  Else
    Print 'False on < 1'
    
  Set @MyCondition = 2
  If @MyCondition >= 1 AND @MyCondition < 1
    Print 'True on > 1'
  Else
    Print 'False on > 1'
End


False on = 1
False on < 1
False on > 1

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-12 : 13:24:55

JG777 should first let us know the datatype of the addDate column.... otherwise there's no end to this discussion ..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources