DateTime conversion issue in SQL Server 2000

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

DateTime conversion issue in SQL Server 2000

Author

Topic

royster
Starting Member

5 Posts

Posted - 2009-04-17 : 08:04:22

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.
[CODE](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[/CODE]

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
[CODE]DECLARE @LastSync DateTime
SET @LastSync = CAST((SELECT Value FROM App_Data WHERE Item = 'IntranetAccountLastSync') AS DateTime)
SELECT * FROM DNCreatedDateAndGroups
WHERE Createdon > @LastSync[/CODE]

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

If I run
[CODE]SELECT *, IsDate(CreatedOn) FROM DNCreatedDateAndGroups[/CODE]

The IsDate column returns all rows with 1

If I run
[CODE]SELECT * FROM DNCreatedDateAndGroups WHERE IsDate(createdOn) = 1[/CODE]
I get
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

If I then try to run
[CODE]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[/CODE]
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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-17 : 08:32:52

Post the result of this

SELECT Value FROM App_Data WHERE Item = 'IntranetAccountLastSync'

Madhivanan

Failing to plan is Planning to fail

royster
Starting Member

5 Posts

Posted - 2009-04-17 : 08:39:05

Madhivanan The result is

01/01/2009 00:00:00

since this is a field in a variable table I know this is stored as a varchar, but I do convert it.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-17 : 08:45:01

I think you have some bad dates though isdate() returns 1. Because isdate() is not fully reliable

select isdate(2000)

What is the format that you store dates in the varchar column?

Madhivanan

Failing to plan is Planning to fail

royster
Starting Member

5 Posts

Posted - 2009-04-17 : 08:59:31

The data is stored as yyyymmddhhmmss.0D e.g. 20090417111523.0D

When I create the view I use 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)) to convert it to DateTime.

I have manually scanned through the date values stored, and I cannot see any bad dates. The only one that comes close is 29/02/2008 which should be valid as it is a leap year. If all my SQL code for converting is correct then it might be AD storing the data. The alternative I have is that perhaps when I run SELECT * FROM DNCreatedDateAndGroups WHERE IsDate(createdOn) = 1 the createdOn field used to compare is the raw column entry 20090417111523.0D as opposed to the date converted 17/04/2009 11:15:23 in the view? I'm not sure when the Where clause kicks in.

royster
Starting Member

5 Posts

Posted - 2009-04-17 : 10:20:51

Madhivanan
Here is a head scratcher for you re this issue. I have just changed the whole sql to take the results from the View and insert them into an in memory table @TranslateTable, then perform the SQL from the 'Temp Table' and it works. The Dates seem to be ok as they get entered into the DateTime field in the 'temp table' without causing an error.


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

DECLARE @TranslateTable TABLE (
CreatedOn DateTime,
RoleName varchar(1024) NULL,
Initials varchar(64) NULL
)

INSERT INTO @TranslateTable (CreatedOn,RoleName,Initials)
SELECT     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')


SELECT * FROM @TranslateTable
WHERE CreatedOn > @LastSync

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-18 : 03:02:46

So, you got it worked?

Madhivanan

Failing to plan is Planning to fail

royster
Starting Member

5 Posts

Posted - 2009-04-20 : 04:06:04

Yes it works. Just confused as to why it worked one way and not the other. Oh well it works and that is the important thing.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-20 : 08:51:10

quote:
Originally posted by royster

Yes it works. Just confused as to why it worked one way and not the other. Oh well it works and that is the important thing.

Ok. Thanks

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources