[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Get Properly Formatted Date From ASP.net membership profile using fn_GetProfileElement

Posted on 2009-04-20
8
Medium Priority
?
540 Views
Last Modified: 2013-11-08
Hello,

I have a web app that uses the ASP.net Membership provider and I am currently working with a view that extracts the profile values into separate columns using the fn_GetProfileElement function.  Everything so far works great except for any Profile fields that are dates.  In the view, my datetime fields are shown as the XML string and not just the date.  

For example, if I call the follwing SQL:

SELECT dbo.fn_GetProfileElement('Birthdate', PropertyNames, PropertyValuesString) As Birthdate
FROM aspnet_Profile

The result is the birthday's for all my users in the following format:

<?xml version="1.0" encoding="utf-16"?>
<dateTime>2009-04-18T08:00:00</dateTime>

What I really need is for it to just be just the date in the standard SQL format.  Anyone know how to get this done?

TIA

Mike
0
Comment
Question by:MClarkCCR
  • 4
  • 4
8 Comments
 
LVL 3

Author Comment

by:MClarkCCR
ID: 24187381
I will also settle for a UDF that would strip the xml tags right out of the string.
0
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 2000 total points
ID: 24188146
This is a rather simple parser function that I added some date code to so it would give just the date part.
CREATE FUNCTION udfParseTag(@XMLString VARCHAR(MAX),@tag VARCHAR(100))
RETURNS
VARCHAR(MAX)
AS
BEGIN
	DECLARE  @startTag VARCHAR(50), @endTag VARCHAR(50)
	SET @startTag = '<'+@tag+'>'
	SET @endTag = '</'+@tag+'>'
	RETURN (SELECT CONVERT(VARCHAR,CONVERT(DATETIME,SUBSTRING(@XMLString,CHARINDEX(@startTag,@XMLString)+LEN(@startTag),CHARINDEX(@endTag,@XMLString,CHARINDEX(@startTag,@XMLString))-(CHARINDEX(@startTag,@XMLString)+LEN(@startTag)))),101));
END
GO
-- test it
DECLARE @x VARCHAR(100) , @tag VARCHAR(50)
SET @x = '<a><dateTime>2009-04-18T08:00:00</dateTime>'
SET @tag = 'dateTime'
SELECT dbo.udfParseTag(@x,'dateTime')

Open in new window

0
 
LVL 3

Author Comment

by:MClarkCCR
ID: 24189657
CGLuttrell,

Thank you very much for the code, this looks like it could work but I am at home at the moment and will not be able to test the function until I return to the office tomorrow.  One thing that I see missing is the
<?xml version="1.0" encoding="utf-16"?>
tag that precedes all of the datetime entries.  Do you think your function would parse this tag out as well, or will additional code be required?

Thanks,

Mike
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24189890
No additional code required. It finds the first occurance of the tag requested skipping over anything before it.
0
 
LVL 3

Author Comment

by:MClarkCCR
ID: 24196894
Well I've tried the function using the following code in my select statement


dbo.fn_ParseTag(dbo.fn_GetProfileElement(N'Birthdate', PropertyNames, PropertyValuesString), 'dateTime') AS Birthdate

to find the Birthdate in the profile and I'm greeted with an error:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

I'm not really sure what is causing it, my guess would be either the fact that the XML date has a T in the middle of the date and time or that some of my rows have NULL for birthdate.  Any thoughts on how I could modify the code to handle either?

Mike
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24197068
I tested it with the string above that included tht T in the XML date and it worked.  What version of SQL Server are you on?  That might be the problem and I will have to tweak the function.  I tested it on 2008 and 2005, are you on 2000?
0
 
LVL 3

Author Comment

by:MClarkCCR
ID: 24199218
We managed to figure out what was causing the problem.  Your code worked fine to handle the dates and nulls, the only exception is that some of the date fields have default values of 1/1/0001, which I believe is out of the range of valid SQL dates.  Luckily the app is still in development, so we simply deleted the test users with the offending data, and made sure to set the default values in the profile to 1/1/1900.

Thanks alot, your function helped get us past that most annoying hurdle!

Mike
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24199578
Glad I could help and thanks for the feedback.  Good luck with your project.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question