how do i read a fuzzy date from MS SQL? [modified]
-
*solved* this is what you get for posting questions at 8:30pm local time *sleepy* after some more digging i have noticed a second column that has a int, 0, 1 or 2, and it exactly correlates with the "fuzzy date". so a second flag is being used to achieve this *** i am trying to convert data from one system (stored in MS SQL) into a different system stored in MySQL. in the MS SQL some records are stored with a "fuzzy date", they only has a year, no day and no month. we have the same concept the new system, where we store the date as 00/00/1988 using a SQL client on MySQL with one of these dates i am told it is 01/01/1988, but if i issue the same query on the MySQL command line i am told the date is 00/00/1988, so i have to distrust what clients are telling me. in the MS SQL database i have tried "enterprise manager version 8" and it is telling me the date is 01/01/1988, but the software (i have NO access to any source code for the old system) reports the date as "1988", no day and no month. either the old system uses some flag somewhere to say "this is a fuzzy date" or the MS SQL client is "protecting" me from the invalid data. in MSSQL the column is of type "datetime", and i have found dates that show as "01/01/1985" and "03/2001" in the old system, so i can rule out the theory that you cannot record data on the 1st January. any pointers or bright ideas would be most welcome. i know something about MySQL but am learning MSSQL as i go along :rolleyes:
zen is the art of being at one with the two'ness -- modified at 15:34 Thursday 15th June, 2006
-
*solved* this is what you get for posting questions at 8:30pm local time *sleepy* after some more digging i have noticed a second column that has a int, 0, 1 or 2, and it exactly correlates with the "fuzzy date". so a second flag is being used to achieve this *** i am trying to convert data from one system (stored in MS SQL) into a different system stored in MySQL. in the MS SQL some records are stored with a "fuzzy date", they only has a year, no day and no month. we have the same concept the new system, where we store the date as 00/00/1988 using a SQL client on MySQL with one of these dates i am told it is 01/01/1988, but if i issue the same query on the MySQL command line i am told the date is 00/00/1988, so i have to distrust what clients are telling me. in the MS SQL database i have tried "enterprise manager version 8" and it is telling me the date is 01/01/1988, but the software (i have NO access to any source code for the old system) reports the date as "1988", no day and no month. either the old system uses some flag somewhere to say "this is a fuzzy date" or the MS SQL client is "protecting" me from the invalid data. in MSSQL the column is of type "datetime", and i have found dates that show as "01/01/1985" and "03/2001" in the old system, so i can rule out the theory that you cannot record data on the 1st January. any pointers or bright ideas would be most welcome. i know something about MySQL but am learning MSSQL as i go along :rolleyes:
zen is the art of being at one with the two'ness -- modified at 15:34 Thursday 15th June, 2006
Look for ISDATE(), CAST(), CONVERT() DATEPART() and DATENAME() functions in BOL. Farhan Noor Qureshi if (this == this) thow this;