SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


datetime column conversion


datetime column conversion

Author
Message
bryan van ritter
bryan van ritter
SSC Veteran
SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)

Group: General Forum Members
Points: 268 Visits: 513
Hi,

I have a tabel with 3 columns. One of them is column expiredate which is of datetime value. The table was filled from a csv file. The column now has entries like: 2011-04-11 00:00:00.000
What is the best way of updating this column to the dutch standard: DD-MM-YY without the timestamp.
I've tried some and this is the error i get;
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

thanks,
Bryan
GSquared
GSquared
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31255 Visits: 9730
If the column is datetime data type, then the layout "in the table" doesn't matter. It's actually stored as a number, and the way you see it depends on settings in whatever application you're using to look at it with.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Brandie Tarvin
Brandie Tarvin
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20322 Visits: 9118
Ditto what GSquared said. Don't worry about the data in the table. Display is where that sort of things matter.

If you absolutely must tinker with the table, though, look into SQL Server collations. You might be able to apply a column collation that will change the look of the date data in the table. But that's an awful lot of trouble to go to. It complicates maintenance and will confuse later DBAs (especially if you don't document a rogue column collation). The best bet is to leave your tables and columns collated with the OS collation unless you have no other choice.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
bryan van ritter
bryan van ritter
SSC Veteran
SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)

Group: General Forum Members
Points: 268 Visits: 513
Hi,

thanks both of you for the reply. i'll just do that as you suggested.

bryan
Peter Lavelle-397610
Peter Lavelle-397610
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 742
As the previous posters said the column in the table should be kept as datetime and it's a matter of presentation as to how it's displayed.

The following will get your date returned in dd-mm-yy format:

SELECT CONVERT(CHAR(8), yourDateTimeCol, 5) FROM yourTable...

The 5 is a style (see CONVERT function in BOL).
bryan van ritter
bryan van ritter
SSC Veteran
SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)

Group: General Forum Members
Points: 268 Visits: 513
Hi ,

Thanks for the reply. I already had something like that:

select convert(char(11), columname,105) from tablename.

but thank anyway for mentioning it.

Bryan
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search