How can I convert this string into a datetime in MS SQL Server 2000?

Question

How can I convert a char value that I receive from an external data source with a value of '2006926' (supposed to represent September 26 2006 ) to a datetime datatype? Note the '0' is missing from the month. I can not find a way to convert this to a datetime datatype because of the missing '0'. None of the converts or casts I tried could handle this. Is there a way?

Answer 1

If your day parameter is ever less than two characters, your data-architect should be shot, as there would be no way to distinguish 2006111 from jan.11, or nov. 1. Let's assume that is not an issue.

You will always have either a 7 or 8 character string.

create proc sp_MakeDate @pMyString varchar(10)
as
declare @pMyDate smalldatetime
select @pMyDate = case

when len(rtrim(@pMyString)) = 7 then substring(@pMyString, 5, 1) + '/' + substring(@pMyString, 6, 2) + '/' + left(@pMyString,4)

when len(rtrim(@pMyString)) = 8 then substring(@pMyString, 5, 2) + '/' + substring(@pMyString, 7, 2) + '/' + left(@pMyString,4)

end

print @pMyDate
GO

Answer 2

i'd have to know what you're using to send the data to sql. (visual basic? ASP?)

i can only advise you in vb/asp, but maybe you can translate the concept to whatever you're using.

===========================
function convertdate(incomingdate)

dim tempdate
tempdate=incomingdate

select case mid(tempdate,5,2)

case "01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12"

tempdate=mid(tempdate,5,2) & "/" & right(tempdate,2) & "/" & left(tempdate,4)

case else

tempdate="0" & mid(tempdate,5,1) & "/" & right(tempdate,2) & "/" & left(tempdate,4)

end select

convertdate=tempdate

end function
===========================

the first case scenario checks to see if the month value is already valid and switches around the pieces of the string value to make it look like a date.

the second case scenario catches anything that does not represent a proper month value (single digit) and places a zero in front of the single digit before composing the date.

NOTE: are you certain that your last two digits (day of the month) will be two characters? if not, you'll need to build a select case to catch those, similar to the one that corrects the month.

Answer 3

My suggestion would to take the incoming char and put the whole number into an array , and then just manaully add the 0 the end of the array (ie AFTER the last digit)

Then from the array, you can convert the number into a string and then from the string to date-time stamp

Answer 4

sq. Server has 6 distinctive date and time datatypes, smalldatetime, datetime, datetime2, datetimeoffset, date, and time. one and all has distinctive accuracy from one hundred nanoseconds to one million day.