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