converting varchar to datetime

Question

converting varchar to datetime

SQLalchemy

SSC-Addicted

Points: 486
More actions
August 29, 2014 at 1:43 pm

#298961

I have the following schema:
create table names (name varchar(50), DOB varchar(8), age int,
address varchar(50), city varchar(20),
state varchar(2), zip varchar(5), phone varchar(10));
insert names values ('William Johnson', '07191940', 74,
'262 Conner Jay Rd.', 'Brevard',
'NC', '26419', '8281254483');
insert names values ('Kay Johnson', '04101942', 71,
'262 Conner Jay Rd.', 'Brevard',
'NC', '26419', '8281258459');
I'm tryiing to, and I know it's not good to store dates as varchar, but I will fix 🙂 , convert DOB e.g. '04101940' to a datetime if for no other reason than to be able to easily calculate age... end result something like 10/24/1940 or anythhing that will allow calculations... here's my SQL I'm currently using:
SELECT LEFT(Name, CHARINDEX(' ', Name)) AS 'First Name',
SUBSTRING(Name,(LEN(LEFT(Name, CHARINDEX(' ', Name)))+2),1) as mi,
RIGHT(Name, CHARINDEX(' ', REVERSE(Name))) AS 'Last Name',
Substring(DOB,1,2) + '/'
+ Substring(DOB,3,2) + '/'
+ Substring(DOB,5,4) AS DOB,
CONVERT(varchar(10), cast(DOB as datetime),101) DOBrevision,
age,
LEFT(address, CHARINDEX(' ', address)) AS 'House Number',
SUBSTRING(address,(LEN(LEFT(address, CHARINDEX(' ', address)))+2),20) as Street,
city, state, zip,
'(' + Substring(phone,1,3) + ') '
+ Substring(phone,4,3) + '-'
+ Substring(phone,7,4) AS Phone
from names
order by age desc
The "CONVERT(varchar(10), cast(DOB as datetime),101) DOBrevision," gives an error of "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value"
Thanks for any help!

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Why are you converting to datetime and then back to varchar? Why varchar instead of char?

SQL Server won't recognize the MMDDYYYY format as a valid date, you need to supply the slashes and preferably use convert with the format code.

Here's something to help you out.

SELECT Substring(DOB, 1, 2) + '/' + Substring(DOB, 3, 2) + '/' + Substring(DOB, 5, 4) AS DOB

,STUFF( STUFF( DOB, 3, 0, '/'), 6, 0, '/') AS DOBAlternateFormula

,CONVERT(datetime, STUFF( STUFF( DOB, 3, 0, '/'), 6, 0, '/') , 101) AS DOBDatetime

,CONVERT(VARCHAR(10), CONVERT(datetime, STUFF( STUFF( DOB, 3, 0, '/'), 6, 0, '/') , 101), 101) DOBrevision

FROM names

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Interesting thing about storing the DOB as a character string in mmddyyyy format, it is easy to flip it to yyyymmdd and then convert to a date:

create table dbo.names (name varchar(50), DOB varchar(8), age int,

address varchar(50), city varchar(20),

state varchar(2), zip varchar(5), phone varchar(10));

insert dbo.names values ('William Johnson', '07191940', 74,

'262 Conner Jay Rd.', 'Brevard',

'NC', '26419', '8281254483');

insert dbo.names values ('Kay Johnson', '04101942', 71,

'262 Conner Jay Rd.', 'Brevard',

'NC', '26419', '8281258459');

go

select

n.name,

n.DOB,

cast(right(n.DOB,4) + left(n.DOB,4) as date) NewDOB,

n.age,

n.address,

n.city,

n.state,

n.zip,

n.phone

from

dbo.names n;

go

drop table dbo.names;

go

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 3

Quick thought, use a combination of replace and convert with the 110 format directive

😎

USE tempdb;

GO

DECLARE @MMDDYY VARCHAR(20) = '01 20 1950'

SELECT CONVERT(DATETIME,REPLACE(@MMDDYY,CHAR(32),CHAR(45)),110)

Result

1950-01-20 00:00:00.000