Date conversion problem

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • MrMancunian
    Recognized Expert Contributor
    • Jul 2008
    • 569
    #1

    Date conversion problem

    Hi,

    I'm trying to insert some data in a table in SQL Server 2005, including a date. When I insert 29-07-2008 in the datetime field, it saves it as 25-7-1894 0:00:00. Can anyone tell me why it's doing that? The complete query is as follows:

    Code:
    INSERT INTO tblBCelLymfomen (BepalingID, BCelLymfomenDatumUitslag, BCelLymfomenKwaliteitBepaling, BCelLymfomenKwaliteitDNA, BCelLymfomenUitslag, BCelLymfomenConclusie, BCelLymfomenConclusie2, IgHAOnverdundGrootte, IgHBOnverdundGrootte, IgHCOnverdundGrootte, IgHDOnverdundGrootte, IgHEOnverdundGrootte, IgKappaAOnverdundGrootte, IgHAOnverdundBeschr, IgHBOnverdundBeschr, IgHCOnverdundBeschr, IgHDOnverdundBeschr, IgHEOnverdundBeschr, IgKappaAOnverdundBeschr, IgKappaBOnverdundGrootte, IgLambdaAOnverdundGrootte, IgKappaBOnverdundBeschr, IgLambdaAOnverdundBeschr) VALUES (1796, CONVERT(datetime, 29-07-2008, 120), '-', '-', 'Tests', '-', 'Tests', '1', '2', '3', '4', '5', '6', 'G', 'P', 'P(w)', 'R', 'R/P', 'Rw', '7', '8', 'Rw/P', 'D')
    Thnx,

    Steven
    Tags: None
    • OuTCasT
      Contributor
      • Jan 2008
      • 374
      #2
      are u using a DateTimePicker as the control

        Comment

        • MrMancunian
          Recognized Expert Contributor
          • Jul 2008
          • 569
          #3
          Originally posted by OuTCasT
          are u using a DateTimePicker as the control
          No, the data is coming from an Access 2003 database. I tried using both the raw data from the database and a DateTime-datatype.

          Steven

            Comment

            • Delerna
              Recognized Expert Top Contributor
              • Jan 2008
              • 1134
              #4
              Try changing this
              [code=sql]
              CONVERT(datetim e, 29-07-2008
              [/code]

              to this
              [code=sql]
              CONVERT(datetim e, '29-07-2008'
              [/code]

              or this
              [code=sql]
              CONVERT(datetim e, #29-07-2008#
              [/code]

              I'm thinking 29-07-2008 might be getting read as
              29 minus 7 minus 2008 = -1986

              25-7-1894 0:00:00 looks an awful lot like what happens when you save a number to a date field. The beginning of time according to microsoft is midnight on 01-01-1900
              and i bet that date -1986 days (5.5 years) = 25-7-1894

                Comment

                • MrMancunian
                  Recognized Expert Contributor
                  • Jul 2008
                  • 569
                  #5
                  Originally posted by Delerna
                  Try changing this
                  [code=sql]
                  CONVERT(datetim e, 29-07-2008
                  [/code]

                  to this
                  [code=sql]
                  CONVERT(datetim e, '29-07-2008'
                  [/code]

                  or this
                  [code=sql]
                  CONVERT(datetim e, #29-07-2008#
                  [/code]

                  I'm thinking 29-07-2008 might be getting read as
                  29 minus 7 minus 2008 = -1986

                  25-7-1894 0:00:00 looks an awful lot like what happens when you save a number to a date field. The beginning of time according to microsoft is midnight on 01-01-1900
                  and i bet that date -1986 days (5.5 years) = 25-7-1894
                  The quotes worked. Thnx! :)

                    Comment

                    Previous template Next
                    Working...