| Author |
Topic |
|
js.reddy
Yak Posting Veteran
80 Posts |
 Posted - 2007-11-16 : 07:01:46
|
| Hi All!I have following table columnJoiningDate-----------------------2007-11-16 17:23:34.0302007-11-15 17:24:13.2972007-11-14 17:24:18.1102007-11-13 17:24:23.810I want convert the above column to the following formatCan any one help meThanksJoiningDate-----------------------2007-11-16 00:00:00.0002007-11-15 00:00:00.0002007-11-14 00:00:00.0002007-11-13 00:00:00.000 |
|
|
hitman
Starting Member
23 Posts |
Posted - 2007-11-16 : 07:32:00
|
| maybe you can use datepart & dateadd combinationdeclare @new_date datimeset @new_date = '2007-11-13 17:24:23.810'set @new_date = dateadd(hh, - datepart(hh, @new_date), @new_date)set @new_date = dateadd(mi, - datepart(mi, @new_date), @new_date)set @new_date = dateadd(ss, - datepart(ss, @new_date), @new_date)set @new_date = dateadd(ms, - datepart(ms, @new_datet), @new_date)-----------------------http://www.sqltips.info----------------------- |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-11-16 : 07:36:35
|
| SELECT DATEADD(d, DATEDIFF(d, 0, GetDate()), 0)_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
hitman
Starting Member
23 Posts |
Posted - 2007-11-16 : 07:38:39
|
| this is better :-)-----------------------http://www.sqltips.info----------------------- |
|
|
|
js.reddy
Yak Posting Veteran
80 Posts |
Posted - 2007-11-16 : 07:39:36
|
Thanks  quote:
Originally posted by spirit1
SELECT DATEADD(d, DATEDIFF(d, 0, GetDate()), 0)_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com
|
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-11-19 : 02:18:23
|
quote:
Originally posted by spirit1
SELECT DATEADD(d, DATEDIFF(d, 0, GetDate()), 0)_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com
Just for clarity I prefer using day in place of d SELECT DATEADD(day, DATEDIFF(day, 0, GetDate()), 0)MadhivananFailing to plan is Planning to fail |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-11-19 : 03:32:38
|
| i prefer to type less _______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-11-19 : 09:11:01
|
I prefer not to type. Just click copy and click paste
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2007-11-19 : 12:13:56
|
| Can also be done with CAST & CONVERTCAST(CONVERT(varchar(11),GETDATE()) AS datetime) |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-11-19 : 13:44:17
|
quote:
Originally posted by visakh16
Can also be done with CAST & CONVERTCAST(CONVERT(varchar(11),GETDATE()) AS datetime)
but the dateadd / datediff method is fastest
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-11-20 : 01:37:27
|
quote:
Originally posted by visakh16
Can also be done with CAST & CONVERTCAST(CONVERT(varchar(11),GETDATE()) AS datetime)
There are N number of methods in factMadhivananFailing to plan is Planning to fail |
|
|
|
|
converting date