Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
hummy
Starting Member
32 Posts |
 Posted - 2008-06-30 : 15:39:14
|
| Hi,My website runs the following command against the sql server database. selectconvert(varchar,agent.[CUSTOM TABLE 1 Forms received on],103) [Forms Received On],fromVRP_CUSTOM_TABLE_1 agentThe problem i have is that the results are displayed in a grid which can be sorted by simply clicking the grid. Now it seems becuase the results are retunred as varchar the dates are not sorted properly in the website, as you have some 2007 records before 2008and others after 2008. The date needs to be returned as a date in the format DD/MM/YYYY. The forms recieved on column is a DATETIME column.Can anyone recommend a way of showing these results as a date time field but in the format DD/MM/YYYY.I was looking on another site and found the following query but it doesn't seem to work for me. cast(convert(varchar(100), mydatecol, 101) as datetime) AS mydatecolApparently, telling SQL Server to convert the datetime to a varchar and then back to a datetime does the trick.Thanks in advance. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
hummy
Starting Member
32 Posts |
Posted - 2008-06-30 : 15:58:55
|
| Yes...i would love to do that...except i don't know how to. My developer has unfortunately left me in the lurch somewhat with unfinished work. I can see in the code the bit that has the sql statement so i was hoping i could just edit that. |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-06-30 : 16:13:28
|
| where is this "grid" that is displaying the data? Is this ASP.NET, ASP, VB, a report, or something else? That is where you should apply your formatting, not by converting all of your data to VARCHAR. Just about all programming languages and reporting tools have quick and easy "right-click to format" options where you can usually enter a format string like YYYY-MM-DD.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
hummy
Starting Member
32 Posts |
Posted - 2008-06-30 : 16:24:30
|
| Hi.It's in ASP.NET and i can see the code from within Visual Studio 2005 as i'm no programmer and not used VS2005 before. However how i find the properties of this Grid i do not know....Perhaps you can point me the right direction. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
hummy
Starting Member
32 Posts |
Posted - 2008-06-30 : 17:08:20
|
| fantastic...thank you very much. |
|
|
|
|
|
|
|
|
Extract Date from DateTime field as Date Time