How to Format date without "Convert " function in T-SQL

[arti]

I dont want to use Convert(Char(9),date,106) function to show date in dd/MM/yyyy format. It changes the datatype of my column to char & I cant perform other date operations on it without changing it back to datetime.
Is there any T-sql way of doing it?

[iburyak]

Why would you care what format date has when you do data manipulation.
Use convert function only in select close when you try to display this date to a user.

SELECT Convert(Char(9),date,106) Date
FROM some_table 
WHERE date > dateadd(d, -10, getdate())
ORDER BY date

Good Luck.

[Motoma]

You are trying to get the datetime formatted in a non-standard format but still be able to use it in the date?

I would suggest creating a second column with the altered format, use that for viewing, and use the original for altering, updating, and calculating.

[ronicard]

A couple of other options:
A bit verbose, but you can do this:

select
dtForm = cast(day(dateField) as varchar) + '/' + cast(month(dateField) as varchar) + '/' + cast(year(dateField) as varchar)

obviously, "dateField" is the name of whatever date field you're pulling from the database. If you still wanted that as a date, you could always recast that back to datetime with the cast function, just like I was doing with the varchar.

[greedy4chips]

If you are doing it in a datagrid, just change the defaultcellstyle of the date column. "d" is the short date format mm/dd/yyyy

Me.dataGridView1.Columns("NameOfDateColumn").Defau ltCellStyle.Format = "d"

The cell will still hold the datetime value from the database, but will only show the converted date to the user.

[manpakhong]

Why would you care what format date has when you do data manipulation.
Use convert function only in select close when you try to display this date to a user.

SELECT Convert(Char(9),date,106) Date
FROM some_table 
WHERE date > dateadd(d, -10, getdate())
ORDER BY date

Good Luck.

Excuse me, can you tell me why char(9) is used for an argument for Convert()
Thanks

Dave

[manpakhong]

this link has the details explanation:
http://www.dreamincode.net/forums/showtopic42872.htm

[e-mail]

Excuse me, can you tell me why char(9) is used for an argument for Convert()
Thanks

Dave