Solved

MS SQL server Date format issues

Posted on 2009-03-31
38
477 Views
Last Modified: 2012-05-06
I have a bit of a problem with the date formats in DotNetNuke. It all started when we imported several hundred users into our database which have several DOB fields. Problem is, the CSV file converted some of the dates in the DOB fields to dd/mm/yyyy format whilst leaving others in a yyyy-mm-dd format. I am now trying to create a calculated column for age using the getdate() function and it is a mess!. Is there a way I can convert all formats of the entries into a single standardized format? For instance, I would like them all to be yyyy-mm-dd regardless of how they are stored now. Then I need to set the new profile fields to be calculated using the getdate() function. DNN does not have a setting where I can use a calculation, so I will need to set these in the db manually.  

The second issue I have is posting this data while using a PIVOT. The code below is what I am using to generate columns from rows is the DNN userprofiles table. I believe I will need to create new profile properties to accommodate the new calculated age columns. This isn't an issue if that is the case. However I also need to filter these results depending on the users role, which comes from yet another table. The only profiles I want to return are members of the subscribers role which has a roleID of '2'
Select	

UserID, 

[21] as FirstName, 

[23] as LastName,

[39] as DOB,

--new age field goes here--

[40] as Gender, 

[43] as FM1, 

[44] as FM1DOB, 

--new age field goes here--

[45] as FM1Gender, 

[47] as FM2, 

[48] as FM2DOB, 

--new age field goes here--

[49] as FM2Gender, 

[50] as FM3, 

[51] as FM3DOB, 

--new age field goes here--

[52] as FM3Gender, 

[53] as FM4, 

[54] as FM4DOB,

--new age field goes here-- 

[55] as FM4Gender, 

[56] as FM5, 

[57] as FM5DOB, 

--new age field goes here--

[58] as FM5Gender, 

[59] as FM6, 

[60] as FM6DOB, 

--new age field goes here--

[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

Open in new window

0
Comment
Question by:DDH1
  • 17
  • 12
  • 5
  • +1
38 Comments
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 250 total points
ID: 24031058
The problem is that you are attempting to store datetime value in a varchar field.  You need to store your data in a datetime field if you want any hope of having the data be useful.

For help on converting the dates to/from various formats, reference this article I wrote.

http://sqlservernation.com/blogs/howtos/archive/2009/03/05/changing-the-display-format-of-datetime-values.aspx
0
 

Author Comment

by:DDH1
ID: 24031990
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--
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24032272
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.
0
 

Author Comment

by:DDH1
ID: 24032626
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?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24032743
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.
0
 

Author Comment

by:DDH1
ID: 24032819
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 ?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24033052
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.
0
 

Author Comment

by:DDH1
ID: 24033263
OK will do. For the record, is there a find/replace syntax u can use to replace the '-' with '/'?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24033520
replace(InWhatField, '-','/')
0
 

Author Comment

by:DDH1
ID: 24033769
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?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24033865
The replace goes inline with your select.

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

Author Comment

by:DDH1
ID: 24034261
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 '-'
0
 

Author Comment

by:DDH1
ID: 24034630
Nevermind...I got it with
UPDATE UserProfile
SET PropertyValue = REPLACE(propertyvalue,'-','/')
WHERE PropertyDefinitionID = 39
0
 

Author Comment

by:DDH1
ID: 24034854
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.
0
 
LVL 31

Expert Comment

by:James Murrell
ID: 24054827
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...


0
 
LVL 83

Assisted Solution

by:CodeCruiser
CodeCruiser earned 250 total points
ID: 24058683
You can use the Convert function in the following format

Convert(DateTime, columnname, format) where format could be used to change the datetime as you like whether its stored as datetime or varchar.

http://msdn.microsoft.com/en-us/library/ms187928.aspx
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24059589
This should work:

UPDATE UserProfile
SET PropertyValue = convert(varchar(32), convert(datetime, propertyvalue, 103), 111)
WHERE PropertyDefinitionID = 39
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:DDH1
ID: 24060249
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
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24060324
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.
0
 

Author Comment

by:DDH1
ID: 24060633
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?
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24060699
Yes.
>while still keeping it in nvarchar format...right?
NO. It will convert it into DateTime.
0
 

Author Comment

by:DDH1
ID: 24060781
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.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24060844
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.
0
 

Author Comment

by:DDH1
ID: 24060929
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.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24061538
sql_variant will store nvarchar and varchar, it won't store nvarchar(max).
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24061831
You can again convert the datatime to nvarchar like this

Convert(NVarChar(32), Convert(DateTime, columnname, 103))
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24062206
cc... Yup.  That's why I posted it above.
0
 

Author Comment

by:DDH1
ID: 24062216
OK, I can see we are getting nowhere fast here so I am going to close this question.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24062302
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.
0
 

Author Comment

by:DDH1
ID: 24062390
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"
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24062458
"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.
0
 

Author Comment

by:DDH1
ID: 24062582
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.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 24063233
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.
0
 

Author Comment

by:DDH1
ID: 24063308
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.
0
 

Author Comment

by:DDH1
ID: 24063399
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--
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

760 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

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now