Querying date

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • ndn_24_7
    #1

    Querying date

    Hello all,
    I'm trying to run a query to make a report. My database is a incident
    reporting database. I'm tryng to make a monthy report for incidents.
    The field I need to query in the date field which is a nvarchar in the
    form of 01/01/04 and 01/01/2004. I ran a query that looks like this:
    SELECT incident, doccur, ID
    FROM dbo.Incident
    WHERE (doccur between '01/01/2004' and '01/31/2004')

    I get some results that look like this:

    Unsecured doors 01/19/04 92
    INTOXICATION 01/17/04 77
    Bill Door entry door 01/28/03 130
    Hit & Run 01/21/04 105
    Customer complaint 01/02/03 70
    Customer complaint 01/02/04 91
    PRINTER MALFUNCTION 01/22/04 111
    Customer complaint 01/30/04 2322
    Trash Smoldering 01/15/04 51
    LOST 01/02/03 80
    BROKEN GLASS PANEL 01/13/04 42
    B.I.A. Assist 01/04/03 189
    GAS LEAK 01/06/04 8
    UNCHANGED CASH BOX 01/11/04 40
    Intoxication 01/17/04 69
    Intoxication 01/02/04 71
    Intoxication 01/17/04 72
    Employee accident 01/17/04 73
    GREASE FIRE 01/18/04 74
    Verbal Dispute 01/17/04 75
    PANHANDLING 01/17/04 76
    Near Miss/Water backup 01/18/04 78
    Unsecured Arcade Door 01/19/04 93
    Intoxication 01/18/04 79
    Intoxication 01/02/04 81
    SUSPECT/WANTED 01/18/04 82
    Intoxication 01/18/04 83
    Property Damage 01/20/03 84
    Unsecured Bingo Snack Bar 01/18/04 85
    PANHANDLING 01/18/04 86
    Employee accident 01/19/04 87
    Unauthorize of proper exit 01/19/04 88
    Safety Hazard 01/19/04 89
    Key control violation 01/02/03 90
    Cracked keno ball 01/23/04 116
    Employee accident 01/19/04 94
    delay in drop 01/27/2003 128
    test 01/01/2005 3763



    As you can see, the querey will give me the month and day I ask for,but
    not the right year. Some to the data has 2 digit years and some have 4
    digits. How do I design the query to give me the year I ask for.
    Any assistance will be greatly appreciated

    Tags: None
    • Simon Hayes
      #2
      Re: Querying date


      "ndn_24_7" <ndn_24_7@yahoo .com> wrote in message
      news:1105646861 .118444.238060@ f14g2000cwb.goo glegroups.com.. .[color=blue]
      > Hello all,
      > I'm trying to run a query to make a report. My database is a incident
      > reporting database. I'm tryng to make a monthy report for incidents.
      > The field I need to query in the date field which is a nvarchar in the
      > form of 01/01/04 and 01/01/2004. I ran a query that looks like this:
      > SELECT incident, doccur, ID
      > FROM dbo.Incident
      > WHERE (doccur between '01/01/2004' and '01/31/2004')
      >
      > I get some results that look like this:
      >
      > Unsecured doors 01/19/04 92
      > INTOXICATION 01/17/04 77
      > Bill Door entry door 01/28/03 130
      > Hit & Run 01/21/04 105
      > Customer complaint 01/02/03 70
      > Customer complaint 01/02/04 91
      > PRINTER MALFUNCTION 01/22/04 111
      > Customer complaint 01/30/04 2322
      > Trash Smoldering 01/15/04 51
      > LOST 01/02/03 80
      > BROKEN GLASS PANEL 01/13/04 42
      > B.I.A. Assist 01/04/03 189
      > GAS LEAK 01/06/04 8
      > UNCHANGED CASH BOX 01/11/04 40
      > Intoxication 01/17/04 69
      > Intoxication 01/02/04 71
      > Intoxication 01/17/04 72
      > Employee accident 01/17/04 73
      > GREASE FIRE 01/18/04 74
      > Verbal Dispute 01/17/04 75
      > PANHANDLING 01/17/04 76
      > Near Miss/Water backup 01/18/04 78
      > Unsecured Arcade Door 01/19/04 93
      > Intoxication 01/18/04 79
      > Intoxication 01/02/04 81
      > SUSPECT/WANTED 01/18/04 82
      > Intoxication 01/18/04 83
      > Property Damage 01/20/03 84
      > Unsecured Bingo Snack Bar 01/18/04 85
      > PANHANDLING 01/18/04 86
      > Employee accident 01/19/04 87
      > Unauthorize of proper exit 01/19/04 88
      > Safety Hazard 01/19/04 89
      > Key control violation 01/02/03 90
      > Cracked keno ball 01/23/04 116
      > Employee accident 01/19/04 94
      > delay in drop 01/27/2003 128
      > test 01/01/2005 3763
      >
      >
      >
      > As you can see, the querey will give me the month and day I ask for,but
      > not the right year. Some to the data has 2 digit years and some have 4
      > digits. How do I design the query to give me the year I ask for.
      > Any assistance will be greatly appreciated
      >[/color]

      The use of nvarchar for dates is the real issue - if you clean up the dates
      and change the data type to datetime, then you should have no problems, and
      you will be able to use date functions correctly with the data. I appreciate
      of course that you may have little control over the data model, but as
      you've already seen, using the wrong data type just leads to incorrect or
      inconsistent data, so it would be worth some effort to correct this.

      The following query may work, but it depends on the quality of your data:

      SELECT incident, doccur, ID
      FROM dbo.Incident
      WHERE cast(doccur as datetime) between '20040101' and '20040131'

      Note that the format YYYYMMDD is always interpreted correctly by MSSQL,
      whereas the US date format is ambiguous.

      Simon


        Comment

        • Andy Williams
          #3
          Re: Querying date

          In addition to Simon's comments, have a look at
          http://www.aspfaq.com/show.asp?id=2280


          -Andy

          "ndn_24_7" <ndn_24_7@yahoo .com> wrote in message
          news:1105646861 .118444.238060@ f14g2000cwb.goo glegroups.com.. .[color=blue]
          > Hello all,
          > I'm trying to run a query to make a report. My database is a incident
          > reporting database. I'm tryng to make a monthy report for incidents.
          > The field I need to query in the date field which is a nvarchar in the
          > form of 01/01/04 and 01/01/2004. I ran a query that looks like this:
          > SELECT incident, doccur, ID
          > FROM dbo.Incident
          > WHERE (doccur between '01/01/2004' and '01/31/2004')
          >
          > I get some results that look like this:
          >
          > Unsecured doors 01/19/04 92
          > INTOXICATION 01/17/04 77
          > Bill Door entry door 01/28/03 130
          > Hit & Run 01/21/04 105
          > Customer complaint 01/02/03 70
          > Customer complaint 01/02/04 91
          > PRINTER MALFUNCTION 01/22/04 111
          > Customer complaint 01/30/04 2322
          > Trash Smoldering 01/15/04 51
          > LOST 01/02/03 80
          > BROKEN GLASS PANEL 01/13/04 42
          > B.I.A. Assist 01/04/03 189
          > GAS LEAK 01/06/04 8
          > UNCHANGED CASH BOX 01/11/04 40
          > Intoxication 01/17/04 69
          > Intoxication 01/02/04 71
          > Intoxication 01/17/04 72
          > Employee accident 01/17/04 73
          > GREASE FIRE 01/18/04 74
          > Verbal Dispute 01/17/04 75
          > PANHANDLING 01/17/04 76
          > Near Miss/Water backup 01/18/04 78
          > Unsecured Arcade Door 01/19/04 93
          > Intoxication 01/18/04 79
          > Intoxication 01/02/04 81
          > SUSPECT/WANTED 01/18/04 82
          > Intoxication 01/18/04 83
          > Property Damage 01/20/03 84
          > Unsecured Bingo Snack Bar 01/18/04 85
          > PANHANDLING 01/18/04 86
          > Employee accident 01/19/04 87
          > Unauthorize of proper exit 01/19/04 88
          > Safety Hazard 01/19/04 89
          > Key control violation 01/02/03 90
          > Cracked keno ball 01/23/04 116
          > Employee accident 01/19/04 94
          > delay in drop 01/27/2003 128
          > test 01/01/2005 3763
          >
          >
          >
          > As you can see, the querey will give me the month and day I ask for,but
          > not the right year. Some to the data has 2 digit years and some have 4
          > digits. How do I design the query to give me the year I ask for.
          > Any assistance will be greatly appreciated
          >[/color]


            Comment

            • ndn_24_7
              #4
              Re: Querying date

              Thank you for your reply,

              I would agree that nvarchar for my date is a problem. Would anybody
              know how to convert the dates to 4 digit years thru a query or do I
              have to go change every date manually?After I change them to the
              correct format, I'm assuming I can change the datatype to datetime.
              Some of my date are 02/21/04 and some are 02/21/2004. The program has a
              Access front end, So i guess I could use the find and replace function.
              Any other suggestions will really help

                Comment

                • Andy Williams
                  #5
                  Re: Querying date

                  ALTER TABLE incident ADD DateOccured datetime

                  UPDATE incident SET DateOccured = CAST(doccur AS datetime)


                  "ndn_24_7" <ndn_24_7@yahoo .com> wrote in message
                  news:1105648821 .121164.51910@f 14g2000cwb.goog legroups.com...[color=blue]
                  > Thank you for your reply,
                  >
                  > I would agree that nvarchar for my date is a problem. Would anybody
                  > know how to convert the dates to 4 digit years thru a query or do I
                  > have to go change every date manually?After I change them to the
                  > correct format, I'm assuming I can change the datatype to datetime.
                  > Some of my date are 02/21/04 and some are 02/21/2004. The program has a
                  > Access front end, So i guess I could use the find and replace function.
                  > Any other suggestions will really help
                  >[/color]


                    Comment

                    • ndn_24_7
                      #6
                      Re: Querying date

                      One more question. Would I have to change the dates to 2004-02-21 in
                      order to change the datatype to datetime?

                        Comment

                        • ndn_24_7
                          #7
                          Re: Querying date

                          I was able to convert all my dates to datetime. Now I can query by
                          date and all information is accurate.Thank you all so much for you
                          assistance.I greatly appreciat it.

                            Comment

                            • Erland Sommarskog
                              #8
                              Re: Querying date

                              ndn_24_7 (ndn_24_7@yahoo .com) writes:[color=blue]
                              > I would agree that nvarchar for my date is a problem. Would anybody
                              > know how to convert the dates to 4 digit years thru a query or do I
                              > have to go change every date manually?After I change them to the
                              > correct format, I'm assuming I can change the datatype to datetime.
                              > Some of my date are 02/21/04 and some are 02/21/2004. The program has a
                              > Access front end, So i guess I could use the find and replace function.
                              > Any other suggestions will really help[/color]

                              It seems that you have sorted out your dates by now, but nevertheless
                              some addditional information.

                              Like many other programs, SQL Server plays some guessing games based
                              on some settings. In case of SQL Server these are dateformat and
                              language, which are peculiare to SQL Server and not the regional
                              settings. '02/21/04' may give you what you want, it may give you
                              a different date in a different year, and you might even end up in
                              the wrong century, if you have a funny configuration of your server.

                              Anyway, here is an article that gives you the full story:
                              http://www.karaszi.com/SQLServer/info_datetime.asp.



                              --
                              Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

                              Books Online for SQL Server SP3 at
                              SQL Server 2025 | Microsoft Evaluation Center
                              http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
                              Accelerate your AI application's time to market by harnessing the power of your own data and the built-in AI capabilities of SQL Server 2025, the enterprise database with best-in-class security, performance and availability.

                                Comment

                                Previous template Next
                                Working...