DateTime conversion issue in SQL Server 2000

[royster]

Hi
I have a strange problem with SQL Server 2000 that I have never come across before and wondering if anyone can help.

I am exporting Active directory information into a database using DSDE every hour. The data is stored in a 3 tier 1 to many relationship.

searchResultEntry (searchResultEntry_Id int, dn, varchar(1024)) stores the distinguishedname and an Id for each
attr (attr_id int, name varchar(64), searchResultEntry_Id int) for each dn in searchResultEntry, stores the names of the attributes retrieved and an Id for each
value (attr_Id int, value_Text varchar(512)) stores the corresponding values for each of the attributes for each dn

I have an application that needs to get AD groups for each user and sync with another database, but I only want to get the AD groups that were created since the last sync took place.

The problem I am having is that the value_Text is varchar field. I am retrieving a value for datetime stored in AD for the Date the AD account was created. The data is stored as yyyymmddhhmmss.0D e.g. 20090417111523.0D.

I have created a view which gets the dn, converts my varchar date to a datetime format, the Groups the dn belongs to and the AD Accountname.

(SELECT     dbo.searchResultEntry.dn, CONVERT(datetime, LEFT(CAST(value_Created.value_Text AS varchar(14)), 8) 
                      + ' ' + SUBSTRING(CAST(value_Created.value_Text AS varchar(14)), 9, 2) + ':' + SUBSTRING(CAST(value_Created.value_Text AS varchar(14)), 11, 2) 
                      + ':' + SUBSTRING(CAST(value_Created.value_Text AS varchar(14)), 13, 2)) AS CreatedOn, value_memberOf.value_Text AS Groups, 
                      UPPER(value_Initials.value_Text) AS Initials
FROM         dbo.searchResultEntry INNER JOIN
                      dbo.attr attr_Created ON dbo.searchResultEntry.searchResultEntry_Id = attr_Created.searchResultEntry_Id INNER JOIN
                      dbo.[value] value_Created ON attr_Created.attr_Id = value_Created.attr_Id INNER JOIN
                      dbo.attr attr_memberOf ON dbo.searchResultEntry.searchResultEntry_Id = attr_memberOf.searchResultEntry_Id INNER JOIN
                      dbo.[value] value_memberOf ON attr_memberOf.attr_Id = value_memberOf.attr_Id INNER JOIN
                      dbo.attr attr_Initials ON dbo.searchResultEntry.searchResultEntry_Id = attr_Initials.searchResultEntry_Id INNER JOIN
                      dbo.[value] value_Initials ON attr_Initials.attr_Id = value_Initials.attr_Id
WHERE     (attr_Created.name = 'whenCreated') AND (attr_memberOf.name = 'memberOf') AND (attr_Initials.name = 'sAMAccountName')) AS DNCreatedDateAndGroups

Now I want the view to return me all the accounts and their associated groups since the last sync. The trouble is that my DateTime field doesn't seem to allow a date comparison in the Where clause.

If I run

DECLARE @LastSync DateTime
SET @LastSync = CAST((SELECT Value FROM App_Data WHERE Item = 'IntranetAccountLastSync') AS DateTime)
SELECT * FROM DNCreatedDateAndGroups
WHERE Createdon > @LastSync

I get
Server: Msg 241, Level 16, State 1, Line 3
Syntax error converting datetime from character string.


If I run

SELECT *, IsDate(CreatedOn) FROM DNCreatedDateAndGroups

The IsDate column returns all rows with 1

If I run

SELECT * FROM DNCreatedDateAndGroups WHERE IsDate(createdOn) = 1

I get
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.


If I then try to run

DECLARE @LastSync DateTime
SET @LastSync = CAST((SELECT Value FROM App_Data WHERE Item = 'IntranetAccountLastSync') AS DateTime)
select * from DNCreatedDateAndGroups
where CASE WHEN isdate(createdon)=1 THEN createdon ELSE getdate() END > @LastSync

I get
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Yet IsDate returned all dates to be valid....I am now stumped as to how to proceed.

Can anyone help? Thanks

[dee-u]

Have you tried casting CreateOn as DateTime?

CAST(CONVERT(DATETIME, LEFT(CAST(value_Created.value_Text AS VARCHAR(14)), 8) + ' ' + SUBSTRING(CAST(value_Created.value_Text AS VARCHAR(14)), 9, 2) + ':' + SUBSTRING(CAST(value_Created.value_Text AS VARCHAR(14)), 11, 2) + ':' + SUBSTRING(CAST(value_Created.value_Text AS VARCHAR(14)), 13, 2)) AS DateTime) AS CreatedOn