Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I would caution against changing the data from UTC to local time.</p> <p><code>SqlMembershipProvider</code> returns <code>MembershipUser.LastLoginDate</code> and other similar properties in local time: i.e. it converts the database value from UTC to local.</p> <p>So if you're accessing this data via the <code>Membership</code> API, you don't need to do this.</p> <p>If you're accessing the data in the database directly, and you really, really want SQL Server to return it in local time, why not just convert it when you're reading from the database.</p> <p>For example, you could create a VIEW on the aspnet_Membership table something like:</p> <pre><code>SELECT ... LastLoginDate + GETDATE() - GETUTCDATE() AS LastLoginDateLocal ... FROM aspnet_Membership </code></pre> <p>Note that by doing the conversion in SQL Server, you'll be getting the local time of the SQL Server, which may not be the same as the local time on the machine hosting your application.</p> <p><strong>UPDATE</strong> </p> <p>From comments:</p> <blockquote> <p>I want to know why @Leo approach didn't work.</p> </blockquote> <p>@Leo suggested modifying the <code>aspnet_Membership_UpdateUserInfo</code> SP; you need to modify all SPs that update the columns you're interested in. For example, <code>LastLoginDate</code> is also updated by <code>aspnet_Membership_UpdateUser</code>.</p> <p>Also, if you follow this approach, the time returned in <code>MembershipUser.LastLoginDate</code> property will be incorrect, since the <code>SqlMembershipProvider</code> code assumes the database value is in UTC. To correct this problem, you would need to modify all the Membership SPs that SELECT this column, to convert back to UTC. E.g. <code>aspnet_Membership_GetAllUsers</code>, <code>aspnet_Membership_FindUsersByEmail</code>, <code>aspnet_Membership_FindUsersByName</code>, ...</p> <p>Note also that converting between UTC and local in SQL Server as above will sometimes give incorrect values (+/- 1h) due to DST (e.g. the difference GETDATE() - GETUTCDATE() is calculated during a period when DST is in operation, but the user last logged in before DST started).</p> <p>Another reason why it's better to leave the database value in UTC, and do any conversion in your application.</p>
    singulars
    1. This table or related slice is empty.
    1. POIs is possible to change the date format of the LastLoginDate in the aspnet_membership table?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USJoe
    1. USJoe
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks @Joe this is an interesting approach. Since I already know what the time difference is going to be I simply called the LastLoginDate column as `DATEADD(hour, 5, DATEADD(minute, 30, dbo.aspnet_Membership.LastLoginDate)) AS LastLoginDate` and I got the adjusted time via the view. I wouldn't be marking your answer as the accepted answer just yet though; I want to know why @Leo approach didn't work.
      singulars
      1. PO
      1. USRusty Wizard
    2. CO@RustyWizard, see update.
      singulars
      1. PO
      1. USJoe
    3. COI reflected the change in the following SPs: aspnet_Membership_CreateUser, aspnet_Membership_ResetPassword, aspnet_Membership_SetPassword, aspnet_Membership_UpdateUserInfo, aspnet_Membership_UpdateUser but it still didn't work. The reason I'm trying to do it at a database level is because these values won't be retrieve via the application. Users (db admins) will have to look at the DB itself to get the values so having it in UTC might cause a bit of confusion.
      singulars
      1. PO
      1. USRusty Wizard
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload