How to convert ddmmyy into SQL datetime

catherinelouise
catherinelouise used Ask the Experts™
on
Hello,

I have an IVR which takes in DTMF values for a date in the format ddmmyy.

I then want to update a date field with those values - how do I go about convert the string into a datetime field?  I'm finding I can't use Convert(Datetime....  because it is expecting the input in a different format.

Likewise if I take in, for example, 1450 to represent a time, how do I get that into a table field to log the time?

Thanks in advance....
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
try this
declare @dt as varchar(6)
set @dt = '141108'
select cast(substring(@dt,1,2) + substring(@dt,3,2) + substring(@dt,5,2) as datetime)
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
you have to work the string a bit:

DECLARE @data VARCHAR(6)
SET @data = '131108'
SELECT @data, CONVERT(datetime, LEFT(@data, 2) + '.' + SUBSTRING(@data,3,2) + '.' + SUBSTRING(@data, 5,2), 4) date_value

Open in new window

Author

Commented:
Sweeet - thanks angelll

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial