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 |
|
Gyto
Posting Yak Master
144 Posts |
 Posted - 2007-11-16 : 10:53:36
|
| Hi there,Is there a script I can run from Query analyser to change the date format of a column? I'd like to change it as follows:From: 31/01/1970 00:00:00To: 31/01/1970So I just want to lose the time....any ideas?ThanksMatt |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-11-16 : 10:59:00
|
If your datatype is datetime, the time portion will always be there. Do the formatting of datetime in your front end application.
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
Gyto
Posting Yak Master
144 Posts |
Posted - 2007-11-16 : 11:05:46
|
| That's what I would normally do, the only problem is I created a new database from a backup which did not have the time portion (because it had been removed by the frontend) but the restored copy obviously has the time portion back again |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-11-16 : 11:07:58
|
what is the data type of the column ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
Gyto
Posting Yak Master
144 Posts |
Posted - 2007-11-16 : 11:10:28
|
| It's datetime in SQL Server |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-11-16 : 11:19:34
|
you can't really remove the time from a datetime. It is always there. You cannot really "remove" it, you can only set it to midnight time 00:00:00 only.So here, quote:
From: 31/01/1970 00:00:00To: 31/01/1970
What you want here is just a presentation issue. as for quote:
the only problem is I created a new database from a backup which did not have the time portion (because it had been removed by the frontend) but the restored copy obviously has the time portion back again
I am not sure what do you mean by "it did not have the time portion". As long as the data type is datetime, the time is always there.You can however set the time portion of the datatime to midnight 00:00:00 using the following querydateadd(day, datediff(day, 0, datecol), 0)
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
Gyto
Posting Yak Master
144 Posts |
Posted - 2007-11-16 : 11:22:15
|
| Ok thanks...I just meant the time portion was not displayed, so it only showed the date. I'll have a play :) Cheers |
|
|
|
|
|
|
|
|
Modifying table datetime format