datetime column conversion

Question

datetime column conversion

bryan van ritter

SSCrazy

Points: 2270
More actions
February 15, 2011 at 10:28 am

#232260

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

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 SSC Guru Points: 172994 More actions · Answer 2

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/[/url]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 SSCrazy Points: 2270 More actions · Answer 3

Hi,

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

bryan

Peter Lavelle-397610 SSC Veteran Points: 215 More actions · Answer 4

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 SSCrazy Points: 2270 More actions · Answer 5

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