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 |
|
learntsql
524 Posts |
 Posted - 2009-10-19 : 06:31:10
|
| Hi All,How to remove time value from datetime field and result should be valid date.eg:2009-10-19 15:56:51.030 ---> 2009-10-19 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-10-19 : 06:35:58
|
quote:
Originally posted by learntsql
Hi All,How to remove time value from datetime field and result should be valid date.eg:2009-10-19 15:56:51.030 ---> 2009-10-19
Where do you want to show the formatted date?MadhivananFailing to plan is Planning to fail |
 |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-10-19 : 06:36:24
|
| Do this in the front end. If you really want to do this in SQL, if you are using 2008, you can use the DATE datatype. If not then you can do one of the following:To keep it as a datetime, use:select Dateadd(dd,0,datediff(dd,0,'2009-10-19 15:56:51.030'))Or if you want to just display the date, you have to convert it to a char:select convert(varchar(10),'2009-10-19 15:56:51.030') although if you use this for joins, you will not be able to take advantage of any indexes. |
|
|
|
learntsql
524 Posts |
Posted - 2009-10-19 : 06:45:29
|
| I use in my report developing in ssrs 2005 from there i export to EXCELI tried with RickD'sselect Dateadd(dd,0,datediff(dd,0,'2009-10-19 15:56:51.030'))but its showing time value with a;; zeros even i need to remove zeros. |
|
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2009-10-19 : 06:49:05
|
| SELECT CONVERT(VARCHAR(20), GETDATE(), 102)SELECT CONVERT(VARCHAR(20), GETDATE(), 106)SELECT CONVERT(VARCHAR(20), GETDATE(), 110) |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-10-19 : 06:55:14
|
quote:
Originally posted by learntsql
I use in my report developing in ssrs 2005 from there i export to EXCELI tried with RickD'sselect Dateadd(dd,0,datediff(dd,0,'2009-10-19 15:56:51.030'))but its showing time value with a;; zeros even i need to remove zeros.
In SSRS, there must be option to do formationMadhivananFailing to plan is Planning to fail |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-10-19 : 07:01:37
|
| You can convert it in reporting services itself on the format for the field. |
|
|
|
learntsql
524 Posts |
Posted - 2009-10-19 : 07:16:16
|
| yes,Thank you for ur reply.I managed in ssrs only. |
|
|
|
|
|
|
|
|
remove time value from datetime?