Created By
DMG Development
As Seen On

Main Page  |  Forums  |  Register  |  Active Topics  |  Members  |  Search

 
Username:
Password:
DMG Forums >> DMG Forums Discussion >> General Discussion >> Conversion of String to DateTime Format Issues
Conversion of String to DateTime Format Issues
Author Topic
grimmeissen


Administrator


Join Date: 8/30/2005
Posts: 656
Location: Cincinnati, Ohio

Posted: 8/21/2006 9:23:32 AM

This is a very common issue that has been answered many times. If you are having issues with your SQL Server date format not matching up with your web site's date format, you will need to modify your web.config file to change the format to whatever the database expects.

For a Microsoft.com article that explains how to modify the web.config file to specify your regional settings, click the link below.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpgenref/html/gngrfglobalizationsection.asp

As an example, the following web.config file would set your application to use the United States English date format.

Code:

<!-- Web.Config Configuration File -->

<configuration>
<system.web>
<compilation defaultLanguage="VB" debug="false" />
<pages validateRequest="false" />
<globalization
culture="en-US"
uiCulture="en-US"
/>
</system.web>
<appSettings>
<add key="DatabaseString" value="..." />
<add key="DatabasePrefix" value="DMG" />
</appSettings>
</configuration>


The link below leads to another Microsoft article that lists all of the available regional codes that can be placed in the globalization tag of your web.config. Use this to determine which code will match the date format of your database.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemGlobalizationCultureInfoClassTopic.asp
edwardrjones


Newbie


Join Date: 6/26/2007
Posts: 4

Posted: 6/28/2007 4:10:26 AM

Hi,
I was having trouble with the active pages loading due to the string to datetime format error, i changed the globalisation in the web.config as you showed above which fixed the problem, however now I have having problems elsewhere in my site because of it, currency and dates shown in US format when I am in the UK. Can you think of another way to stop the active pages showing that error without changing the globalisation for the whole site?

Thanks

Edward


grimmeissen


Administrator


Join Date: 8/30/2005
Posts: 656
Location: Cincinnati, Ohio

Posted: 11/17/2007 9:09:53 PM

You'll need to use the UK globalization culture rather than the US one that is shown in the example.
Flexus


Newbie


Join Date: 11/10/2007
Posts: 9

Posted: 12/17/2007 12:25:53 PM

Quote:
Quoted From grimmeissen:

This is a very common issue that has been answered many times. If you are having issues with your SQL Server date format not matching up with your web site's date format, you will need to modify your web.config file to change the format to whatever the database expects.

For a Microsoft.com article that explains how to modify the web.config file to specify your regional settings, click the link below.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpgenref/html/gngrfglobalizationsection.asp

As an example, the following web.config file would set your application to use the United States English date format.

Code:

<!-- Web.Config Configuration File -->

<configuration>
<system.web>
<compilation defaultLanguage="VB" debug="false" />
<pages validateRequest="false" />
<globalization
culture="en-US"
uiCulture="en-US"
/>
</system.web>
<appSettings>
<add key="DatabaseString" value="..." />
<add key="DatabasePrefix" value="DMG" />
</appSettings>
</configuration>


The link below leads to another Microsoft article that lists all of the available regional codes that can be placed in the globalization tag of your web.config. Use this to determine which code will match the date format of your database.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemGlobalizationCultureInfoClassTopic.asp


Hm, if a change the globalization then I can`t use active topics or delete post. Because I get a date time convension error. My date is like this d/MM/yyyy

Flexus


Newbie


Join Date: 11/10/2007
Posts: 9

Posted: 12/17/2007 12:41:41 PM

arg, this is driving me mad!

joejoe


Newbie

Join Date: 2/25/2008
Posts: 1

Posted: 2/25/2008 5:02:17 PM

test

xaeco


Newbie

Join Date: 2/24/2008
Posts: 4

Posted: 2/26/2008 4:30:46 AM

I have been unable to solve this problem too.

My site is hosted on an American server, but I am in NZ.

If I add the <globalization culture="en-US" uiCulture="en-US"/> to my web config, it stops the date format error but all my times are then displayed in American format and at american time.

If I change web config to <globalization culture="en-NZ" uiCulture="en-NZ" /> my times are correct and in the correct format, but if I try deleting a topic or view active topics I get error message: ERROR [22007] [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting datetime from character string.

grimmeissen


Administrator


Join Date: 8/30/2005
Posts: 656
Location: Cincinnati, Ohio

Posted: 2/28/2008 7:07:48 AM

This has to do with the SQL Server and the web server not storing date-time values the same. If your SQL server is in the United States, the date values will automatically be stored with US format, so you won't be able to change the globalization to NZ format.

I am considering taking away the date formatting from SQL Server and making my own custom functions because this is causing people so many problems. It seems like even though people are overseas, a lot of their servers are setting up SQL with US date formats. I do not know why, but it is very difficult for me to test because SQL automatically sets this up and I am in the USA.
xaeco


Newbie

Join Date: 2/24/2008
Posts: 4

Posted: 2/29/2008 9:17:45 PM

We have managed to figure out a work-around, which may be useful to others so here it is:

In the web config file I have:
<globalization culture="en-NZ" uiCulture="en-NZ"/>

and the database key string
<appSettings>
<add key="DatabaseString" value="Driver={SQL Server};Server=72.52.194.183;Database=xxxxx;Uid=xxxxx;Pwd=xxxxx;"/>
</appSettings>

these were the only changes we made to our existing web config file.


In our global.asax we added this:
Sub Session_Start(ByVal sender As Object, ByVal e As EventArgs)
' How many minutes difference from Server's time to the user's browser time
Session("UserSvrTZO") = 18 * 60 'hours times minutes
End Sub

Which will need to be changed depending on where you are - this is New Zealand time difference to our US Server.

In the forums/default.aspx page we changed the strings to look like this:
<%#LastTopicBy(DataBinder.Eval(Container.DataItem, "(""MEMBER_ID"")"), DataBinder.Eval(Container.DataItem, "(""MEMBER_USERNAME"")"), DMGForums.Global.Database.CorrectOutputDate(DataBinder.Eval(Container.DataItem, "(""FORUM_LASTPOST_DATE"")"), Session("UserSvrTZO")))%>

you will notice above this bit "DMGForums.Global.Database.CorrectOutputDate" which is a function we wrote to correct the dates and take into account any dates which are null.

Use the function like so DMGForums.Global.Database.CorrectOutputDate( date field to change, Session("UserSvrTZO") )

Public Shared Function CorrectOutputDate(ByVal datetm As Object, ByVal AddTo As Double) As Object
If IsDBNull(datetm) Then
Return datetm
End If
Try
Return CDate(datetm).AddMinutes(AddTo)
Catch ex As Exception
Return datetm
End Try

End Function

Other strings which will need to be changed will be found in forums/forums.aspx and forums/forumhome.aspx

As we use MS SQL we then butchered the global.vb database timestamp function and now it looks like this:

Public Shared Function DatabaseTimestamp(Optional ByVal SubtractDays As Integer = 0) As String
Return Format(Now().ToUniversalTime.AddDays(0 - SubtractDays), "yyyy/MM/dd HH:mm:ss")
End Function

and the get timestamp function now looks like this:

Public Shared Function GetTimeStamp(Optional ByVal SubtractDays As Integer = 0) As String
Return Format(Now().ToUniversalTime.AddDays(0 - SubtractDays), "yyyy/MM/dd HH:mm:ss")
End Function

The website will need to be recompiled after changing these files otherwise the dlls seem to override the vb forums and it doesn't see the changes. Alternatively, if you put the convert time functions in a vb file outside the forums this works too.

Hopefully this helps some people! These are great forums, thanks for sharing,


halinab


Newbie

Join Date: 5/23/2008
Posts: 3

Posted: 5/23/2008 5:31:33 AM

ok - I posted in te bug section - sorry.

I changed the config to have globalization of US which fixed the problem - but I want it to be Australian date format - how can that be achieved?

empkey2


Intermediate

Join Date: 2/20/2008
Posts: 74

Posted: 5/24/2008 7:04:58 PM

because this forum app was once not well layered. I am restructuring it to get out those kinds of language problems.




illuminarose


Newbie


Join Date: 4/9/2009
Posts: 1
Location: KSA

Posted: 4/9/2009 8:52:54 AM

Hello I'm afraid I am so totally new at forum development and administration, I've just been browsing here and I can see I have so much to learn from this forum. Although please don't kick me out, I want to learn a great deal from you guys and I believe I will cause this forum has so much to offer and I've barely started yet.

Browsing and learning along the way..

thank you so much..

PBsoft


Newbie

Join Date: 10/18/2009
Posts: 2
Location: Viareggio, Italy

Posted: 10/18/2009 5:52:00 AM

I am trying this beautiful forum application onto my local web and database server (same machine) in my office (i live in Italy).

IIS + MSDE2000

I didn't solve the issue in this topic, neither modifying web.config

How do you think can I solve?


  Copyright DMG Development DMG Forums 3.2