469,568 Members | 1,609 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,568 developers. It's quick & easy.

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

13
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?
Jun 4 '07 #1
6 23879
iburyak
1,017 Expert 512MB
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT Convert(Char(9),date,106) Date
  2. FROM some_table 
  3. WHERE date > dateadd(d, -10, getdate())
  4. ORDER BY date
Good Luck.
Jun 4 '07 #2
Motoma
3,237 Expert 2GB
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.
Jun 4 '07 #3
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.
Jun 12 '07 #4
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.
Jul 6 '07 #5
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT Convert(Char(9),date,106) Date
  2. FROM some_table 
  3. WHERE date > dateadd(d, -10, getdate())
  4. ORDER BY date
Good Luck.

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

Dave
Aug 2 '07 #6
this link has the details explanation:
http://www.dreamincode.net/forums/showtopic42872.htm

manpakhong@hotmail.com


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

Dave
Apr 28 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Westcoast Sheri | last post: by
2 posts views Thread by ethoemmes | last post: by
4 posts views Thread by Patrick McGuire | last post: by
10 posts views Thread by KJ | last post: by
16 posts views Thread by Al Reid | last post: by
4 posts views Thread by Steve Long | last post: by
2 posts views Thread by fniles | last post: by
11 posts views Thread by walterbyrd | last post: by
3 posts views Thread by murch.alexander | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.