DateTime Values in SQL Express ASPNETDB.MDF

greets again folks,

The values LastLoginDate and LastActivityDate in my SQl Express membership dBase are always off.

The date is usually correct but the time is always hours off.

Is there some way to get the time part of the DateTime to be correct?

Do I have to write code to set the time when the user logs in?

Thanks a mil!

It sounds as if GetUtcDate() is being called instead of GetDate. GetUTCDate records the UTC or GMT date time whereas GetDate() get the local date time.

|||

hypercode:

greets again folks,

The values LastLoginDate and LastActivityDate in my SQl Express membership dBase are always off.

The date is usually correct but the time is always hours off.

Is there some way to get the time part of the DateTime to be correct?

Do I have to write code to set the time when the user logs in?

Thanks a mil!

check database coumn type ... is it set to DateTime ....

|||

Kamrul,

Although unlikely, the column does not have to be a DateTime to have GetDate assigned to it. E.g.SELECTCONVERT(VARCHAR(20),GetDate(), 113) returned "19 Apr 2007 17:07:06". but could have inserted the valud into a CHAR(20) column.

|||

The date is OFF in the ASPNETDB itself.

Beoroe I write any code to retireve the values, they are already in the dBase off to begin with.

Is there some way to tell the dBase to record the correct times?

|||

Look in the table definition, do the collumns have the default property change the GetUtcDate() to GetDate(). Do the same in the stored procedures and all date/time from then will be in local rather than universal time.

Incidentally was the time an exact number of hours off from the server time?

|||

" Look in the table definition, do the collumns have the default property change the GetUtcDate() to GetDate(). Do the same in the stored procedures and all date/time from then will be in local rather than universal time. "

I just looked in the table definition for all of the columns which contain datetime date types. I don't see GetUtcDate or GetDate() anywhere in the table definition. Where should these values be displayed?

|||If the data is not being set by a default property, look in the stored procedures for them.|||

Hi Hypercode,

Actually,the datetime value is saved as UTC format in system or database. When there's a request from a user, the server will translate the time into local time which depends on the server's location and response the user's request. So pls be sure that the settings of the timezone on your server is correct ( or just as you want).

If the problem still exists, you have to translate the time manually.Here's the UDF that you might be interested in looking into

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=28712

Hope it helps.

Thanks

|||

Thanks to you guys for pitchin in!

I still didn't get her straightened out yet. Been busy with other stuff (on the same project). I'll be getiing this straightened out though when I get a chance.

DatePart function in ANSI SQL

Hi folks,

How can I re-write the following code in ANSI SQL code:

select cast(datepart(month, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar) + '/' +
cast(datepart(day, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar) + '/'+ cast(datepart(year, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar), event_instance_id, max(time_stamp)
from usmuser.usm_sli_event_data
where event_instance_id=10019
group by cast(datepart(month, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar) + '/' +
cast(datepart(day, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar) + '/'+
cast(datepart(year, dateadd(second, time_stamp, '1/1/1970 00:00:00')) as varchar),
event_instance_id
order by event_instance_id

Thanks for your help!
-Parulcould you please explain why?

also, for those of us not patient enough to unravel the intricacies of this delectable code fragment, would you kindly please explain what it's doing|||The code should be portable so it can used be used on other databases as well, not just SQL Server.
Basically, the time_stamp field has number of seconds since 1/1/1970 and the datepart function is calculating the month, day, and year. The goal is to get the last time_stamp per event_instance_id per day.|||unfortunately, your quest will be unsatisfied

date functions are among the more un-robust of the ANSI SQL capabilities

there is practically no hope that you will get exactly the same code to run "on other databases as well, not just SQL Server"

even if we did manage to figure out a way to do what you're doing with ANSI SQL functions (and good luck to you, as i'm going to pass), it probably wouldn't run on SQL Server to start with|||This is what data abstraction layers are for...|||Thanks r937!

DateDiff function problem

Hi Folks,
Looking for assistance on using the datediff command to convert a date\time
stamp into unix time. A few of you where a great help the last time
directing me in the right direction on doing this. My problem is that the
time stamp is one hour out (one hour ahead) . The below syntax is how
capturing the date and time stamp and converting. I have check my clocking
setting on my server and anything seems fine. I working with British\Irsh
time zone. Anyone any idea where I'm going wrong?
SELECT @.TransactDateCal = (SELECT DATEDIFF(s, '19700101', GetDate ()))
Many thanks,
Liam.Hi
The 1 hour could be due to Summer Time / Daylight Savings Time.
What if you change your server to be exact GMT?
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Liam Mac" wrote:

> Hi Folks,
> Looking for assistance on using the datediff command to convert a date\tim
e
> stamp into unix time. A few of you where a great help the last time
> directing me in the right direction on doing this. My problem is that the
> time stamp is one hour out (one hour ahead) . The below syntax is how
> capturing the date and time stamp and converting. I have check my clocking
> setting on my server and anything seems fine. I working with British\Irsh
> time zone. Anyone any idea where I'm going wrong?
> SELECT @.TransactDateCal = (SELECT DATEDIFF(s, '19700101', GetDate ()))
> Many thanks,
> Liam.
>|||Well, do you observe daylight savings time? Is your machine set up to do
so? Does SELECT GETDATE() yield the right date/time?
"Liam Mac" <LiamMac@.discussions.microsoft.com> wrote in message
news:B197644A-1531-4CB6-A394-69F47FAD74C2@.microsoft.com...
> Hi Folks,
> Looking for assistance on using the datediff command to convert a
> date\time
> stamp into unix time. A few of you where a great help the last time
> directing me in the right direction on doing this. My problem is that the
> time stamp is one hour out (one hour ahead) . The below syntax is how
> capturing the date and time stamp and converting. I have check my clocking
> setting on my server and anything seems fine. I working with British\Irsh
> time zone. Anyone any idea where I'm going wrong?
> SELECT @.TransactDateCal = (SELECT DATEDIFF(s, '19700101', GetDate ()))
> Many thanks,
> Liam.
>|||Or just use GETUTCDATE() instead?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:7CBC1E33-8E30-4CCF-8F96-BD620B31182E@.microsoft.com...
> Hi
> The 1 hour could be due to Summer Time / Daylight Savings Time.
> What if you change your server to be exact GMT?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Liam Mac" wrote:
>
date\time
the
clocking
British\Irsh|||thanks folks, below sytnax worked.
"Adam Machanic" wrote:

> Or just use GETUTCDATE() instead?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:7CBC1E33-8E30-4CCF-8F96-BD620B31182E@.microsoft.com...
> date\time
> the
> clocking
> British\Irsh
>
>