datetime column conversion

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » datetime column conversion

datetime column conversion

Rate Topic

Display Mode

Topic Options

Author

Message

bryan van ritter

Posted Tuesday, February 15, 2011 10:28 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, April 01, 2013 12:35 AM
Points: 82, Visits: 443

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

Post #1064412

GSquared

Posted Tuesday, February 15, 2011 10:31 AM

SSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569

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

Post #1064417

Brandie Tarvin

Posted Tuesday, February 15, 2011 11:03 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:58 AM
Points: 6,655, Visits: 5,678

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 Administrator, MCDBA, MCSA

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Post #1064445

bryan van ritter

Posted Wednesday, February 16, 2011 4:38 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, April 01, 2013 12:35 AM
Points: 82, Visits: 443

Hi,

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

bryan

Post #1064841

Peter Lavelle-397610

Posted Thursday, February 17, 2011 4:33 PM

Grasshopper

Group: General Forum Members
Last Login: Sunday, April 28, 2013 10:07 PM
Points: 12, Visits: 598

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).

Post #1066074

bryan van ritter

Posted Friday, February 18, 2011 1:21 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, April 01, 2013 12:35 AM
Points: 82, Visits: 443

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

Post #1066183

« Prev Topic | Next Topic »

Permissions