asp.net Gridview date format & truncating hyperlink field

in asp.net web application you can choose the Gridview date format by modifying your select query. eg. to get date in dd.mm.yyyy format make your query like this.

Session("CurrentQuery") = "SELECT sub, filename, convert(varchar, cdate, 104) as cdate from circulars ORDER BY cdate DESC"

Now you can modify the convert function as follows

convert(varchar, yourtablecolumnfield, 100) as yourtablecolumnfield - mon dd yyyy hh:mmAM (or PM)
convert(varchar, yourtablecolumnfield, 101) as yourtablecolumnfield - mm/dd/yyyy - 10/02/2008
convert(varchar, yourtablecolumnfield, 102) as yourtablecolumnfield - yyyy.mm.dd as yourtablecolumnfield - 2008.10.02
convert(varchar, yourtablecolumnfield, 103) as yourtablecolumnfield - dd/mm/yyyy
convert(varchar, yourtablecolumnfield, 104) as yourtablecolumnfield - dd.mm.yyyy
convert(varchar, yourtablecolumnfield, 105) as yourtablecolumnfield - dd-mm-yyyy
convert(varchar, yourtablecolumnfield, 106) as yourtablecolumnfield - dd mon yyyy
convert(varchar, yourtablecolumnfield, 107) as yourtablecolumnfield - mon dd, yyyy
convert(varchar, yourtablecolumnfield, 108) as yourtablecolumnfield - hh:mm:ss
convert(varchar, yourtablecolumnfield, 109) as yourtablecolumnfield - mon dd yyyy hh:mm:ss:mmmAM (or PM)
convert(varchar, yourtablecolumnfield, 110) as yourtablecolumnfield - mm-dd-yyyy
convert(varchar, yourtablecolumnfield, 111) as yourtablecolumnfield - yyyy/mm/dd
convert(varchar, yourtablecolumnfield, 112) as yourtablecolumnfield - yyyymmdd
convert(varchar, yourtablecolumnfield, 113) as yourtablecolumnfield - dd mon yyyy hh:mm:ss:mmm
convert(varchar, yourtablecolumnfield, 114) as yourtablecolumnfield - hh:mm:ss:mmm(24h)
convert(varchar, yourtablecolumnfield, 120) as yourtablecolumnfield - yyyy-mm-dd hh:mm:ss(24h)
convert(varchar, yourtablecolumnfield, 121) as yourtablecolumnfield - yyyy-mm-dd hh:mm:ss.mmm
convert(varchar, yourtablecolumnfield, 126) as yourtablecolumnfield - yyyy-mm-ddThh:mm:ss.mmm

Thats it..
Similarly you can use following functions to get desired output in sql express 2008
ASCII, NCHAR, SOUNDEX, CHAR, PATINDEX, SPACE, CHARINDEX, REPLACE, STR, DIFFERENCE, QUOTENAME, STUFF, LEFT, REPLICATE, SUBSTRING, LEN, REVERSE, UNICODE, LOWER, RIGHT, UPPER, LTRIM, RTRIM

To truncate hyperlink field or text when populating grid view.


If you want to truncate hyperlink filed or any text field in grid view control then modify your query like this if you DONT WANT TO SET WRAP PROPERTY IN GRIDVIEW COLUMN

LEFT(sub, 30) + '...' AS sub,

It truncate more then 30 character with '...' . Now you grid will be fixed independent of how much of data is there.

my final query is

"SELECT LEFT(sub, 30) + '...' AS sub,filename, convert(varchar, cdate, 104) as cdate1, cdate from circulars ORDER BY cdate DESC"


Vinod K

Comments

Most Viewed Post

solution for facebook static.ak.fbcdn.net waiting

Connect Sony XPLOD MEX-BT2500 Bluetooth CD Receiver with ur mobile

Crystal Report : Show data horizontally (Left to right) i.e. columns as rows

Google+ Followers