MS SQL server Date format issues

OK, so if I needed to change all of these using your examples, how would I set the Select statement as the propertyID and property values are in two different columns. I changed all the data types to 'date' within the DNN profile controller, which really just brings up the date editor and doesn't set the column data type as it isn't possible due to how DNN stores it's profile properties. I have attached the tables for ref.
--table start--
USE [XXX]
GO
/****** Object:  Table [dbo].[ProfilePropertyDefinition]    Script Date: 03/31/2009 11:23:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProfilePropertyDefinition](
      [PropertyDefinitionID] [int] IDENTITY(1,1) NOT NULL,
      [PortalID] [int] NULL,
      [ModuleDefID] [int] NULL,
      [Deleted] [bit] NOT NULL,
      [DataType] [int] NOT NULL,
      [DefaultValue] [ntext] NULL,
      [PropertyCategory] [nvarchar](50) NOT NULL,
      [PropertyName] [nvarchar](50) NOT NULL,
      [Length] [int] NOT NULL CONSTRAINT [DF_ProfilePropertyDefinition_Length]  DEFAULT ((0)),
      [Required] [bit] NOT NULL,
      [ValidationExpression] [nvarchar](2000) NULL,
      [ViewOrder] [int] NOT NULL,
      [Visible] [bit] NOT NULL,
 CONSTRAINT [PK_ProfilePropertyDefinition] PRIMARY KEY CLUSTERED
(
      [PropertyDefinitionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [dbo].[ProfilePropertyDefinition]  WITH CHECK ADD  CONSTRAINT [FK_ProfilePropertyDefinition_Portals] FOREIGN KEY([PortalID])
REFERENCES [dbo].[Portals] ([PortalID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[ProfilePropertyDefinition] CHECK CONSTRAINT [FK_ProfilePropertyDefinition_Portals]

--table end--

--table start--
USE [XXX]
GO
/****** Object:  Table [dbo].[UserProfile]    Script Date: 03/31/2009 11:24:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserProfile](
      [ProfileID] [int] IDENTITY(1,1) NOT NULL,
      [UserID] [int] NOT NULL,
      [PropertyDefinitionID] [int] NOT NULL,
      [PropertyValue] [nvarchar](3750) NULL,
      [PropertyText] [ntext] NULL,
      [Visibility] [int] NOT NULL CONSTRAINT [DF__UserP__Visib__1352D76D]  DEFAULT ((0)),
      [LastUpdatedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_UserProfile] PRIMARY KEY NONCLUSTERED
(
      [ProfileID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
ALTER TABLE [dbo].[UserProfile]  WITH NOCHECK ADD  CONSTRAINT [FK_UserProfile_ProfilePropertyDefinition] FOREIGN KEY([PropertyDefinitionID])
REFERENCES [dbo].[ProfilePropertyDefinition] ([PropertyDefinitionID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[UserProfile] CHECK CONSTRAINT [FK_UserProfile_ProfilePropertyDefinition]
GO
ALTER TABLE [dbo].[UserProfile]  WITH NOCHECK ADD  CONSTRAINT [FK_UserProfile_Users] FOREIGN KEY([UserID])
REFERENCES [dbo].[Users] ([UserID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[UserProfile] CHECK CONSTRAINT [FK_UserProfile_Users]

--end table--

I'm not familiar with DNN, but I don't like what I see so far.  Storing data in NTEXT and nvarchar(3750) columns, regardless of the type, doesn't make a very database friendly application.  It may be extremely flexible, but flexibility isn't always a good thing.

If it stores all datatypes in NTEXT (not even nvarchar(max)) fields, any type of searching you will need to do will be horrible as you will have to cast to specific data types for doing things like integer or datetime comparison.

I couldn't agree more. So, what about creating a new derived table just bringing in the information I need. For instance the initial select statement I showed you with the PIVOT is for taking all those rows of individual user information and creating a column format for the export all derived from the userprofile table. But, what if I scrapped that and just created a new table that derives the values I need from the userprofile table into its own columns that have the datatype I need to utilize the functions. I just don't know how to create derived tables or even a temp table in an instance like this... what are your thoughts?

A derived table is a select statement that you reference as a table.

example:

select * from
(select 1 as 'theNum')a

In the above "(select 1 as 'theNum')a" is the derived table.

As for a temporary table, the problem that you will run into is that you have data in two different "date" formats.  You need to clean those up into a consistent format in order to be able to cast them as a datetime value.

Ok, I will just go through them manually and set them as the correct desired format. When using the function to get an age, does SQL care if it is yyyy-mm-dd or should I make them all yyyy/mm/dd ?

SQL Server can implicitly cast either of those formats.
select cast('2009/01/01' as datetime)
select cast('2009-01-01' as datetime)

But it is best to specify the format code, which you can get from the link to SQLServerNation I posted above.

OK will do. For the record, is there a find/replace syntax u can use to replace the '-' with '/'?

replace(InWhatField, '-','/')

I tried
SELECT     ProfileID, UserID, PropertyDefinitionID, PropertyValue, PropertyText, Visibility, LastUpdatedDate
FROM         dbo.UserProfile
WHERE     (PropertyDefinitionID = 39)
REPLACE(propertyvalue, '-','/')

but it tells me there is a problem with replace
any thoughts?

The replace goes inline with your select.

SELECT     ProfileID, UserID, PropertyDefinitionID, REPLACE(propertyvalue, '-','/') as PropertyValue, PropertyText, Visibility, LastUpdatedDate
FROM         dbo.UserProfile
WHERE     (PropertyDefinitionID = 39)

Ok that worked but i am unable to edit any of the fields afterwards and if I use the regular select again, all the fields are back to having the '-'

Nevermind...I got it with
UPDATE UserProfile
SET PropertyValue = REPLACE(propertyvalue,'-','/')
WHERE PropertyDefinitionID = 39

Is there a way to run an update to change the format of dd/mm/yyyy to yyyy/mm/dd basically just flipping the date 180. I did google quite a bit and found many articles on this, but nothing that seemed to fit.

in .NET why not try place a  element in your Web.config (under the system.web element) and each user will get the dates formatted to their own preferences.

If you want all users to get a dd/mm/yyyy format, just write  for instance...

This should work:

UPDATE UserProfile
SET PropertyValue = convert(varchar(32), convert(datetime, propertyvalue, 103), 111)
WHERE PropertyDefinitionID = 39

Hi Guy's,
I am not trying to be rude, but I am not seeing where this is answering my original questions. I had to go through the table fields and manually update them all to a format of yyyy/mm/dd . Now that they are all the same, I need to continue on to the initial problem of exporting all necessary fields to another table or select statement where the user roleID='2' using the userexport format listed above.
Thanks

This has answered your first question. You did not need to manualy convert them all to the yyyy/mm/dd format because the solution posted here would have converted the dates automatically for you.

I will keep that in mind for future ref. For the record, this script wold have taken dd/mm/yyyy and converted them to yyyy/mm/dd while still keeping it in nvarchar format...right? I  wish I would have got that answer earlier :) 1206 records later I am finally done with bringing them all into the same date format. I still need to either create another table that is updated via a trighger on the main profile proerty table or a select statement using a pivot to convert all dob fields into age. I am assuming I can alter the statement to [39] as Age instead of [39] as DOB correct?

Yes.
>while still keeping it in nvarchar format...right?
NO. It will convert it into DateTime.

I believe that would have caused me more issues then. The way DotNetNuke stores its information is nvarchar for a reason. The propertyvalue column stores all the profile values, which include but are not limited to Names, addresses and phone numbers. I could be wrong, but I am not familiar with a column conatining two datatypes.

I doubt they are using it, but the SQL_Variant data type will store almost every data type in it's native format.  (it won't store ntext/text, nvarchar(max)/varchar(max).

I could be wrong, but I am not familiar with a column conatining two datatypes.

You are correct Brandon, they are using nvarchar for the column data type(listed in table above). For what reason exactly, I don't know, but I am assuming it is for a great amount of flexability. If the SQL_Variant won't store nvarchar I would assume that is why the designers didn't use it.

sql_variant will store nvarchar and varchar, it won't store nvarchar(max).

You can again convert the datatime to nvarchar like this

Convert(NVarChar(32), Convert(DateTime, columnname, 103))

cc... Yup.  That's why I posted it above.

OK, I can see we are getting nowhere fast here so I am going to close this question.

I have answered EVERY question you have asked.  


Is there a way to run an update to change the format of dd/mm/yyyy to yyyy/mm/dd basically just flipping the date 180. I did google quite a bit and found many articles on this, but nothing that seemed to fit.

UPDATE UserProfile
SET PropertyValue = convert(varchar(32), convert(datetime, propertyvalue, 103), 111)
WHERE PropertyDefinitionID = 39


. If the SQL_Variant won't store nvarchar I would assume that is why the designers didn't use it.

sql_variant will store nvarchar and varchar, it won't store nvarchar(max).


To say that you want to close it "because the experts have not been helpful at all" really irritates me because that couldn't be farther from the truth.

I disagree. The initial questions were regarding the export of fields by the use of a select statement and a PIVOT. I very clearly stated that I needed the script provided modified to accommodate the age for EACH dob field. You responded " You need to store your data in a datetime field if you want any hope of having the data be useful." Which is not true as the script provided by Ralmada does exactly as I needed by converting the dob to an age without changing the column to a new data type. Your answers have been short and cryptic to say the least. So before you go out telling me that EVERY question has been answered and how Irritated you are over my complaint about this question, check the needs again. If anyone is to be irritated by this it is my as I am paying for a service and this is the responses I am getting. Rather then answering my questions, you are sitting there having a fest over who said the most cryptic answer first. So please tell me where you have addressed my export statement. Where are you showing script like I posted above? I further don't care for the insinuation that I am a liar stating things that "couldn't be further from the truth"

"I have a bit of a problem with the date formats in DotNetNuke."
The information I provided in posts http:#a24059589 http:#a24033865 and http:#a24059589


The second issue I have is posting this data while using a PIVOT.
Did I address this part of it, no.  But I figured let's get through one part first.


I'm not claiming that I answered all of your questions from the original post.  But I think we have had an ongoing dialog working towards a final solution for you.  And every question you have asked in response to a post I made I have followed up with an answer.  THAT is what I mean by "I have answered EVERY question you have asked. ".  I didn't say "I have fully resolved your initial questions that you have asked. ".


If you want to have this question deleted, then so be it.  I'm not going to object again.  But you have gotten a lot of value out of this.  And I think it would be more fair to continue to work in this thread, or to award partial points for the value you have received so far.

Brandon, the ONLY thing I have been able to use from this post is
"The replace goes inline with your select.

SELECT     ProfileID, UserID, PropertyDefinitionID, REPLACE(propertyvalue, '-','/') as PropertyValue, PropertyText, Visibility, LastUpdatedDate
FROM         dbo.UserProfile
WHERE     (PropertyDefinitionID = 39)"
Which was NOT the correct syntax as I needed an UPDATE  not a SELECT to alter the fields. I do appreciate the insights to using the REPLACE function however.
If you would like to point out to me what posts you feel have been helpful aside from the above listed, I am all eyes. Getting into a debate over datatypes and conversions has NOT been helpful at all. I am not going to constantly convert data back and fourth to get this done. It is not necessary nor is it (in my opinion) a professional solution.
When you make statements like "I answered EVERY question' then retract stating you didn't mean EVERY question, this further perpetuates a level of unprofessionalism. I don't have a problem keeping this question open and awarding points where there are points due. So far all I have received as very cryptic answers and an attitude. If you wish to work this problem through, then do so by reading the true nature of the question and addressing that. I have already stated that all of the dates are now in a yyyy/mm/dd format so all of this converting in and out of a datetime format is useless. I made that statement BEFORE you continued on and on about the conversion, so it appears you weren't even paying attention to me. Maybe I am wrong. If I seem like I don't get something, maybe explaining it in laymen's terms would have been helpful. I am NOT an SQL expert, hence why I am here PAYING for answers.

The reason you have not been able to use anything from here is that you manually modified the dates in the database before we posted a solution here. Its not a joke mate. People volunteerly devote their time and effort on this forum to help other users. We do not get paid if you accept answer we only get a few points.

CC you are correct in that I updated the fields before I received a response. I did so because I am not at the liberty to wait hoping for an answer. The comment about this not being a joke; again I couldn't agree more and I have not treated this a one. I couldn't be more serious about it. But to continue on about doing a conversion that is no longer needed and thus ignoring what is needed is not a solution. I understand you are volunteers here and my hat is off to you all for that. But my payment every month to EE isn't. If you guy's can't understand my frustrations with this, then I am sorry to hear it.
So, without making this a more painful experience then it already has been; can what I need be done and can you provide the solutions? Simple as that. If not, please tell me so I can delete this question and redirect my efforts elsewhere.

This soluton provided my Ralmada has gotten me the closest to my needs. The shortcommings to it are that I need to filter the results by roleID and thus I must do a cross join on the UserId to the UserRoles table (structure below) AND I need an age field for every dob. The dob fields do not need to be exported as long as the age is there. I am sure they will still need to be part of the select however so the conversion to age can be done.
--export script start--
select a.*, cast(datediff(yy, cast(a.DOB as datetime), getdate()) as nvarchar(4)) as Age
from (
Select      UserID,
[21] as FirstName,
[23] as LastName,
[39] as DOB,
[40] as Gender,
[43] as FM1,
[44] as FM1DOB,
[45] as FM1Gender,
[47] as FM2,
[48] as FM2DOB,
[49] as FM2Gender,
[50] as FM3,
[51] as FM3DOB,
[52] as FM3Gender,
[53] as FM4,
[54] as FM4DOB,
[55] as FM4Gender,
[56] as FM5,
[57] as FM5DOB,
[58] as FM5Gender,
[59] as FM6,
[60] as FM6DOB,
[61] as FM6Gender
FROM
(Select UserID, PropertyDefinitionID, PropertyValue from UserProfile) o    
PIVOT      (     MAX(PropertyValue)     FOR [PropertyDefinitionID] IN ([21], [23], [39], [40], [43], [44], [45], [47], [48], [49], [50], [51], [52], [53], [54], [55], [56], [57], [58], [59], [60], [61])) p
) a
--export script end--

--table start--
USE [XXX]
GO
/****** Object:  Table [dbo].[UserRoles]    Script Date: 04/03/2009 08:57:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserRoles](
      [UserRoleID] [int] IDENTITY(1,1) NOT NULL,
      [UserID] [int] NOT NULL,
      [RoleID] [int] NOT NULL,
      [ExpiryDate] [datetime] NULL,
      [IsTrialUsed] [bit] NULL,
      [EffectiveDate] [datetime] NULL,
 CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED
(
      [UserRoleID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[UserRoles]  WITH NOCHECK ADD  CONSTRAINT [FK_UserRoles_Roles] FOREIGN KEY([RoleID])
REFERENCES [dbo].[Roles] ([RoleID])
ON DELETE CASCADE
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[UserRoles] CHECK CONSTRAINT [FK_UserRoles_Roles]
GO
ALTER TABLE [dbo].[UserRoles]  WITH NOCHECK ADD  CONSTRAINT [FK_UserRoles_Users] FOREIGN KEY([UserID])
REFERENCES [dbo].[Users] ([UserID])
ON DELETE CASCADE
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[UserRoles] CHECK CONSTRAINT [FK_UserRoles_Users]
--table end--