how to convert date type in SQL query output ?

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...

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

You mean like this?:

CONVERT(VARCHAR,RegistrationDate,100)

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

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

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.

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

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

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

<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>

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

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>

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

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)

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

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>

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

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...

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

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()

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

hello santhosh, the datetime in database is already getting stored in UTC

after struggling with this thing i figured out a way as i am using date picker, by using following i am getting correct date and time according to my set time zone.

<%#DateTimeHelper.ConvertToUserTime((DateTime)Eval("RegistrationDate"), DateTimeKind.Utc).ToString()%>

The output is being displayed as: (10/18/2010 3:13:07 PM)

Now can you help me how i can modify the above code to display the output like this -> (Wednesday, October 20, 2010 at 1:36pm) ?

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

To convert database UTC datetime into Server local datetime (RegistrationDate is UTC datetime retrieved from database):

VB.NET:



 


C#:
 <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# String.Format("{0:f}", ((DateTime)Eval("RegistrationDate")).ToLocalTime()) %>' ></asp:Label>
 </ItemTemplate>