July 8, 2009 at 9:12 am
Hello,
this one has me stumped :w00t:
Background. I am currently working on a reporting solution for a large public sector organisation in the UK. This precludes me from posting detailed table definitions, but hopefully I can provide enough information and someone will have seen this before and can tell me what the £$%£$ is going on.
Every week we download csv files from a third party provider which contain data collected by our operatives in the previous week. Any date fields come over in standard ISO 8601 format. E.g. 20090309 03:13:43. I have a script which imports the files into an empty db, in which all the date fields are actually defined as varchars. I then run a stored procedure which imports from the staging tables into the final destination database with the following syntax
Update t1 SET ...
, ...
, t1.PN = t2.PN
, t1.MANAGEBITS = t2.MANAGEBITS
, t1.DATECREATED = cast(t2.DATECREATED as datetime)
, t1.LASTMODIFIED = cast(t2.LASTMODIFIED as datetime)
, t1.FILE_NAME = t2.FILE_NAME
, t1.DATA_START_DATE = cast(t2.DATA_START_DATE as datetime)
, t1.DATA_END_DATE = cast(t2.DATA_END_DATE as datetime)
from [my_Reporting_db].[dbo].[table1] t1
inner join [My_Staging_db].[dbo].[table2] t2
on t1.INDICATORITEMID = t2.INDICATORITEMID
and t1.RECORDID = t2.RECORDID
The columns in the destination db are datetime datatypes.
Last week the vendor updated the software to the latest version. This meant I had to add a few new columns, about 20, to a few different tables. This was achied with a script last Friday. On Monday we tried the import for the first time, and as expected there were issues with other changes they neglected to tell me about. (Columns changing datatype etc.)
But, I have noticed that the Reporting db is now storing dates in a different manner.
If I run these two queries, I get wildly different results
select v.Id, ResponseNumber
from dbo.vw_My_view v
left join dbo.incident i
on v.Id = i.Id
where v.datecreated > '1 Jun 2009'
select v.Id, ResponseNumber
from dbo.vw_My_view v
left join dbo.incident i
on v.Id = i.Id
where v.datecreated > CAST('1 Jun 2009' AS DATETIME)
The first query appears to return all rows from the view, ignoring the where clause completely. The second correctly returns 14 rows.
The view definition is
IDintno410 0 no(n/a)(n/a)NULL
DATECREATEDdatetimeno8 no(n/a)(n/a)NULL
CAPTIONnvarcharno200 yes(n/a)(n/a)Latin1_General_CI_AS
(apologies for the formatting)
so the DateCreated column is definitely a date time. Also, both queries return the following at the end of the RS
IdResponseNumberdatecreated
11220090624-xxxJun 24 2009 4:49PM
11420090627-xxxJun 27 2009 10:10PM
11520090628-xxxJun 28 2009 10:02AM
11620090628-xxxJun 28 2009 1:54PM
11720090630-xxx20090630 11:33:23
11820090705-xxx20090705 04:54:33
(again, apologies for the formatting)
As you can see the last two records, inserted after the upgrade, appear in a different format.
How on earth is it doing that if it is a datetime column? :crazy:
Dave J
July 9, 2009 at 10:00 am
Found it. Although the view was a datetime datatype, the undelying table was changed to nvarchar. I scripted the changes from the staging db and forgot this crucial difference... :blush:
Dave J
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply