how to convert date type in SQL query output ? : The Official Microsoft ASP.NET Forums

Reply

how to convert date type in SQL query output ?

Oct 20, 2010 08:09 PM|LINK

In my database when anyone registers - By default the registration time is saved as UTC universal time zone.

But i want to display everything on the website in (GMT-05:00) Eastern Time (US & Canada)

I am using this SQL QUERY to display registration date:

SELECT TOP (5) Username, CustomerID, RegistrationDate = CONVERT(VARCHAR(12),RegistrationDate,107) FROM Customer ORDER BY RegistrationDate DESC

And, i am displaying it like this:

<asp:Label ID="nameLabeldate" runat="server" Text='<%# Eval("RegistrationDate") %>' />

The problem is the output is getting displayed as : 10/20/2010 1:36:01 PM

I know RegistrationDate is datetime stamp that's why i am getting output in this format.

But i want to display RegistrationDate as: Oct 20, 2010 at 1:36pm -> according to (GMT-05:00) Eastern Time (US & Canada) ORDER BY RegistrationDate in Desc (top most will be last registered user)

I need help with sql query by which i should be able to accoplish my desired output..please help...

www.strivingprogrammers.com

(Online Forums: ASP.NET, SQL, C#, MS Office, Photoshop and much more)

Reply

Re: how to convert date type in SQL query output ?

Oct 20, 2010 08:29 PM|LINK

You mean like this?:

CONVERT(VARCHAR,RegistrationDate,100)

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

-Tab Alleman

Edited by TabAlleman on Oct 20, 2010 08:31 PM

Reply

Re: how to convert date type in SQL query output ?

Oct 20, 2010 08:49 PM|LINK

hello Tab,

i have already tried something like this which solves 1 thing but not other (about time zone)

when i use this query :

SelectCommand="SELECT TOP (5) Username, CustomerID, RegistrationDate = CONVERT(VARCHAR,RegistrationDate,100) FROM Customer ORDER BY RegistrationDate DESC">

it gives output like this: (Oct 18 2010 7:13PM)

But this time that is being displayed is in format of UTC universal time zone. (as it is being saved in database in UTC format)

and i am in eastern time zone so 7:13pm in eastern time zone means 3:13pm (5 hours less) ,

So, what happens is that when i display registration time, it gives 5 hours future time as i am in eastern time zone.

so if user registers at 3:13pm according to (GMT-05:00) Eastern Time (US & Canada), the result shows 7:13pm as future time for registration.

www.strivingprogrammers.com

(Online Forums: ASP.NET, SQL, C#, MS Office, Photoshop and much more)

Reply

Re: how to convert date type in SQL query output ?

Oct 20, 2010 09:00 PM|LINK

I suggest a SQL function that converts UTC to local time. You can write your own or look around the web for one. On my first google, I found this:

http://www.codeproject.com/KB/database/ConvertUTCToLocal.aspx

-Tab Alleman

Reply

limno

All-Star

117314 Points

7997 Posts

Moderator

MVP

Re: how to convert date type in SQL query output ?

Oct 20, 2010 09:30 PM|LINK

<ItemTemplate><asp:Label ID="Label1" runat="server" Text='<%# String.Format("{0:MMMM dd, yyyy h:mm tt}", Eval("RegistrationDate").ToLocalTime()) %>' ></asp:Label></ItemTemplate>

For C# page:

 <ItemTemplate>
                          <asp:Label ID="Label1" runat="server" Text='<%# String.Format("{0:MMMM dd, yyyy h:mm tt}", ((DateTime)Eval("RegistrationDate")).ToLocalTime()) %>' ></asp:Label>
                    </ItemTemplate>

Limno

Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm

Edited by limno on Oct 21, 2010 03:00 AM
Edited by limno on Oct 21, 2010 03:01 AM

Reply

Re: how to convert date type in SQL query output ?

Oct 20, 2010 10:08 PM|LINK

ASP__DEVELOPER
<asp:Label ID="nameLabeldate" runat="server" Text='<%# Eval("RegistrationDate") %>' />

I am assuming that date will not be null.

<asp:Label ID="nameLabeldate" runat="server" 
            Text='<%# DateTime.Parse(Eval("RegistrationDate").ToString()).ToLocalTime().ToLongDateString() %>'>
</asp:Label>

Santhosh

Reply

Re: how to convert date type in SQL query output ?

Oct 21, 2010 12:26 AM|LINK

hello santhosh,

this is what i am getting as output: (Wednesday, October 20, 2010)

which looks great but how i can include time also ?

can i display it like this ? -> (Wednesday, October 20, 2010 at 1:36pm)

www.strivingprogrammers.com

(Online Forums: ASP.NET, SQL, C#, MS Office, Photoshop and much more)

Edited by asp__developer on Oct 21, 2010 12:27 AM

Reply

Re: how to convert date type in SQL query output ?

Oct 21, 2010 12:57 AM|LINK

Try this

<asp:Label ID="nameLabeldate" runat="server"
Text='<%# DateTime.Parse(Eval("RegistrationDate").ToString()).ToLocalTime().ToString() %>'>
</asp:Label>

ASP__DEVELOPER
can i display it like this ? -> (Wednesday, October 20, 2010 at 1:36pm)

You can do it this way

<asp:Label ID="Label1" runat="server" 
            Text='<%# String.Format("{0} at {1}",
                      DateTime.Parse(Eval("RegistrationDate").ToString()).ToLocalTime().ToLongDateString(),
                      DateTime.Parse(Eval("RegistrationDate").ToString()).ToLocalTime().ToLongTimeString()) %>'>
</asp:Label>

Santhosh

Edited by sansan on Oct 21, 2010 01:01 AM
Edited by sansan on Oct 21, 2010 01:01 AM

Reply

Re: how to convert date type in SQL query output ?

Oct 21, 2010 03:13 AM|LINK

hello santhosh,

i tried the above code but the time i am getting is not correct,

the member registered at 10:38pm according to my eastern time zone (-5:00 GMT) but the above code is displaying this time:

(Wednesday, October 20, 2010 at 7:38pm)

which means the time that is being displayed is 3 hours behind my eastern time zone...

www.strivingprogrammers.com

(Online Forums: ASP.NET, SQL, C#, MS Office, Photoshop and much more)

Reply

Re: how to convert date type in SQL query output ?

Oct 21, 2010 04:55 AM|LINK

asp__developer

the member registered at 10:38pm according to my eastern time zone (-5:00 GMT) but the above code is displaying this time:

(Wednesday, October 20, 2010 at 7:38pm)

which means the time that is being displayed is 3 hours behind my eastern time zone...

I think ToLocalTime method gets Universal Time Offset to get the time difference. So, Make sure that you save UTC time when inserting new record. If you are using DateTime to insert date timestamp, use

DateTime.Now.ToUniversalTime();

instead of DateTime.Now. If you are using GETDATE() function, use

GETUTCDATE()

Santhosh

how to convert date type in SQL query output ? RSS

11 replies

ASP__DEVELOP...

how to convert date type in SQL query output ?

TabAlleman

Re: how to convert date type in SQL query output ?

ASP__DEVELOP...

Re: how to convert date type in SQL query output ?

TabAlleman

Re: how to convert date type in SQL query output ?

limno

Re: how to convert date type in SQL query output ?

sansan

Re: how to convert date type in SQL query output ?

ASP__DEVELOPER

ASP__DEVELOP...

Re: how to convert date type in SQL query output ?

sansan

Re: how to convert date type in SQL query output ?

ASP__DEVELOPER

ASP__DEVELOP...

Re: how to convert date type in SQL query output ?

sansan

Re: how to convert date type in SQL query output ?

asp__developer

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft