November 5, 2009 at 9:09 am
Guys,
Would appreciate your help!
I'm trying to convert a ddmmyyyy (23052008) integer to a datetime field using CAST and CONVERT but I'm getting an out of range datetime value?
Could anyone shed any light on this?
Many thanks,
Gary
November 5, 2009 at 9:17 am
You can convert it to a string and then work with the string manipulation functions. Here is an example:
declare @i int
declare @ci char(8)
set @i = 23052008
set @ci = convert(char(8), @i)
select convert(smalldatetime,right(@ci, 4) + substring(@ci, 3, 2) + left(@ci,2))
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 5, 2009 at 9:17 am
Try something like this:
declare @DTInt int, @DTString varchar(20);
select @DTInt = 23052008;
select @DTString = cast(@DTInt as varchar(20));
select cast(stuff(stuff(right(stuff(@DTString, 5, 0, left(@DTString, 2)), 8), 3, 0, '/'), 6, 0, '/') as datetime);
- 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
November 5, 2009 at 9:20 am
Many thanks to you both.
G
November 5, 2009 at 9:21 am
careful on day numbers < 10 though
1Jan2009
will be
1012009 no the expected 01012009
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply