Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

datetime column conversion Expand / Collapse
Author
Message
Posted Tuesday, February 15, 2011 10:28 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, April 26, 2014 6:12 AM
Points: 88, Visits: 468
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
Posted Tuesday, February 15, 2011 10:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 9:58 AM
Points: 13,872, Visits: 9,600
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
Posted Tuesday, February 15, 2011 11:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:46 AM
Points: 5,676, Visits: 6,488
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

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
Posted Wednesday, February 16, 2011 4:38 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, April 26, 2014 6:12 AM
Points: 88, Visits: 468
Hi,

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

bryan
Post #1064841
Posted Thursday, February 17, 2011 4:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, December 14, 2014 9:34 PM
Points: 15, Visits: 698
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
Posted Friday, February 18, 2011 1:21 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, April 26, 2014 6:12 AM
Points: 88, Visits: 468
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 »

Add to briefcase

Permissions Expand / Collapse