Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Comparison using datetime

已答覆 Comparison using datetime

  • Friday, January 11, 2013 6:59 AM
     
     
    Sign In to Vote
    0

    I have a column with datatype bigint in table fctNewRecallCountByMonthBySpecialityByInsGroup  which stores date in format e.g. 20120101, 20120401.

    I have rows for datekey 20100101 and 20100401 but its not returning any rows..

    DECLARE @DATE1 DATETIME,@DATE2 DATETIME
    SET @DATE1= '2010-01-01 00:00:00.000'
    SET @DATE1= '2010-04-01 00:00:00.000'
    SELECT TOP 1 f.NewPatientCount FROM fctNewRecallCountByMonthBySpecialityByInsGroup f 
    JOIN dimInsuranceGroup d on d.dimInsuranceGroupID = f.InsGroupKey 
    WHERE d.InsuranceGrpName = 'PRIVATE' and DateKey 
    between CONVERT(varchar(11),@DATE1,112) and CONVERT(varchar(11),@DATE2,112)

    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

     

All Replies

  • Friday, January 11, 2013 7:06 AM
     
      Has Code
    Sign In to Vote
    0
    Well, a number is a number and not a date. So I don't understand why you're trying to compare it with a VARCHAR? Why converting it at all from a DATETIME literal? 
    DECLARE @DATE1 INT, @DATE2 INT;

SET @DATE1= 20100101;
SET @DATE1= 20100401;

     
  • Friday, January 11, 2013 7:08 AM
     
     
    Sign In to Vote
    0

    Try

    DECLARE @DATE1 DATETIME,@DATE2 DATETIME
    SET @DATE1= '2010-01-01 00:00:00.000'
    SET @DATE1= '2010-04-01 00:00:00.000'
    SELECT TOP 1 f.NewPatientCount FROM fctNewRecallCountByMonthBySpecialityByInsGroup f 
    JOIN dimInsuranceGroup d on d.dimInsuranceGroupID = f.InsGroupKey 
    WHERE d.InsuranceGrpName = 'PRIVATE' and CONVERT(datetime,ltrim(cast(DateKey as varchar)) ,112)
    between @DATE1 and @DATE2

    Many Thanks & Best Regards, Hua Min



     
  • Friday, January 11, 2013 7:11 AM
     
     
    Sign In to Vote
    0
    It is also returning no values ... :(

    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

     
  • Friday, January 11, 2013 7:12 AM
     
     
    Sign In to Vote
    0
    Retry my advice again.

    Many Thanks & Best Regards, Hua Min

     
  • Friday, January 11, 2013 7:13 AM
     
     
    Sign In to Vote
    0
    Because @date1 and @date2 column with datetime are using in another stored procedure and I have to take out the values from this table fctNewRecallCountByMonthBySpecialityByInsGroup  which has dates in a datekey column of type bigint

    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

     
  • Friday, January 11, 2013 7:17 AM
     
     
    Sign In to Vote
    0
    Sorry to say bit this one is also not working... 

    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

     
  • Friday, January 11, 2013 7:19 AM
     
     
    Sign In to Vote
    0
    Sorry to say bit this one is also not working... 

    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

    Then try only this to see if it is returning any data.

    SELECT TOP 1 f.NewPatientCount FROM fctNewRecallCountByMonthBySpecialityByInsGroup f 
    JOIN dimInsuranceGroup d on d.dimInsuranceGroupID = f.InsGroupKey

    Many Thanks & Best Regards, Hua Min

     
  • Friday, January 11, 2013 7:21 AM
     
     
    Sign In to Vote
    0

    Hey Kapil,

    Please verfiy the query,

    u have not set the value for @DATE2 and then u are trying to compare with @date2 in the query which is nothing but i comapares between @date1 value and null which is always false, even the values are set for 2010 but not for 2012

    Chk this below query

    DECLARE @DATE1 DATETIME,@DATE2 DATETIME
    SET @DATE1= '2012-01-01 00:00:00.000'
    SET @DATE2= '2012-04-01 00:00:00.000'
    SELECT TOP 1 f.NewPatientCount FROM fctNewRecallCountByMonthBySpecialityByInsGroup f 
    JOIN dimInsuranceGroup d on d.dimInsuranceGroupID = f.InsGroupKey 
    WHERE d.InsuranceGrpName = 'PRIVATE' and DateKey 
    between CONVERT(varchar(11),@DATE1,112) and CONVERT(varchar(11),@DATE2,112)

    Please have look on the comment


    • Edited by SanthoshH Friday, January 11, 2013 7:25 AM
    •  
     
  • Friday, January 11, 2013 7:23 AM
     
     
    Sign In to Vote
    0

    Yes, this one is returning a value...

    even when i tried:

    DECLARE @DATE1 DATETIME,@DATE2 DATETIME
    SET @DATE1= '2010-01-01 00:00:00.000'
    SET @DATE1= '2010-04-01 00:00:00.000'
    SELECT TOP 1 f.NewPatientCount FROM fctNewRecallCountByMonthBySpecialityByInsGroup f 
    WHERE CONVERT(datetime,ltrim(cast(DateKey as varchar)) ,112) between @DATE1 and @DATE2

    no values are coming as table has values 20100101 and 20100401 in datakey column.

    • Edited by Kapil_KK Friday, January 11, 2013 7:23 AM
    •  
     
  • Friday, January 11, 2013 7:27 AM
     
     
    Sign In to Vote
    0

    Chk this,

    Its wrking fine for me

    DECLARE @TABLE TABLE
    (
    DATES BIGINT
    )

    INSERT @TABLE
    SELECT 20120101 UNION ALL
    SELECT 20120131 UNION ALL
    SELECT 20120201 UNION ALL
    SELECT 20120301 UNION ALL
    SELECT 20120331 UNION ALL
    SELECT 20120401;


    DECLARE @DATE1 DATETIME,@DATE2 DATETIME
    SET @DATE1= '2012-01-01 00:00:00.000'
    SET @DATE2= '2012-04-01 00:00:00.000'
    SELECT CONVERT(varchar(11),@DATE1,112) , CONVERT(varchar(11),@DATE2,112)
    SELECT * FROM @TABLE
    WHERE CONVERT(DateTime,Convert(Varchar,DATES))between @Date1 and @Date2

    Please have look on the comment

     
  • Friday, January 11, 2013 7:29 AM
     
     
    Sign In to Vote
    0

    Try

    SELECT TOP 1 f.NewPatientCount FROM fctNewRecallCountByMonthBySpecialityByInsGroup f
    JOIN dimInsuranceGroup d on d.dimInsuranceGroupID = f.InsGroupKey
    WHERE d.InsuranceGrpName = 'PRIVATE' and DateKey
    between  20100101 and 20100401;

    Many Thanks & Best Regards, Hua Min

     
  • Friday, January 11, 2013 7:55 AM
     
     Answered
    Sign In to Vote
    0

    If the columns are bigint, then you should use bigint and not date:

    DateKey 
    between convert(bigint, CONVERT(varchar(11),@DATE1,112)) and
            convert(bigint, CONVERT(varchar(11),@DATE2,112))

    But since varchar(11) will be implicitly converted to bigint anyway, and you don't get any rows back, there is something else that is wrong. Maybe the data is not what you think.

    Storing date and time values in other data types than the intended one is a recipe for problems.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se