LDAP time to datetime conversion?
-
Hi - I didn't know where to put this. If I have an LDAP time, eg."20060816211120Z", how do I change this so that SQL Server can understand this in datetime format? I'm getting data in the LDAP time format, and I need to send it to another application that only understands datetime.
I was trying convert (varchar, datecolumn,121) but I'm running into errors.
So, I'm thinking that I have to parse it out or something, but I'm having trouble on the parsing.
Any help would be appreciated. Thanks in advance.
-
Okies, I think I got a solution:
update Table1
set newDateTimeField = substring(ldaptimefield,1,4)+'-'+
substring(ldaptimefield,5,2)+'-'+
substring(ldaptimefield,7,2)+' '+
substring(ldaptimefield,9,2)+':'+
substring(ldaptimefield,11,2)+':'+
substring(ldaptimefield,13,2)
The data in the ldaptimefield is text and looks like this 20060816211120Z, etc. The newDateTimeField has datetime field format. So, my above query worked.
If there's another alternative solution, I'm all ears.
-
What about using function like that:
-----------------------------------------------------------
CREATE FUNCTION udf_ConvertLDAPToDatetime (@TextToConvert nvarchar(50))
RETURNS DATETIME
AS
BEGIN
DECLARE @RETURN_DATE DATETIME
DECLARE @DATE DATETIME
DECLARE @HOUR INT
DECLARE @MINUTE INT
DECLARE @SECOND INT
SET @DATE = CONVERT(DATETIME, SUBSTRING(@TextToConvert,1,8),121)
SET @HOUR = SUBSTRING(@TextToConvert,9,2)
SET @MINUTE = SUBSTRING(@TextToConvert,11,2)
SET @SECOND = SUBSTRING(@TextToConvert,13,2)
SET @RETURN_DATE = DATEADD(HOUR,@HOUR,@DATE)
SET @RETURN_DATE = DATEADD(MINUTE,@MINUTE,@RETURN_DATE)
SET @RETURN_DATE = DATEADD(SECOND,@SECOND,@RETURN_DATE)
RETURN (@RETURN_DATE)
END
-----------------------------------------------------------
SELECT DBO.udf_ConvertLDAPToDatetime('20060816211120Z')
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply