Convert text field to Datetime 2

[Vector01]

I have a txt field in an SQL table that I nned to convert to datetime but I need to make it in a date format.
This is what I have now: 10081918 this is what I need it to be 1918/10/08.

I tried using this but MId is not a recognized function.

UPDATE ad1001 SET ad1001.dob = Right([DOB],4) & '/' & Left([DOB],2) & '/' & Mid([DOB],3,2)

Any help greatly appreciated.

 

[tlbroadbent]

Use SUBSTRING in T-SQL instead of MID.

Substring([DOB],3,2)
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Vector01]

I'm getting this error now:

Invalid operator for data type. Operator equals boolean AND, type equals nvarchar.

What should I use to identify the '/'?

Thanks much

 

[tlbroadbent]

T-SQL uses + for concatenation rather than &. Sorry I didn't notice that earlier. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Vector01]

This is the convert string I used and it worked great.

UPDATE ad1001 SET ad1001.dod = Right([DOd],4) + '/' + Left([DOd],2) + '/' + SubString ([DOd],3,2)

I then changed the field from nvarchar to datetime and although it gave a warning that data may be lost it converted sucessfully. Thanks very much. :-D Frodo Baggins
Middle Earth, Hobbiton

 

[Vector01]

Well I spoke to soon. I changed the date delimiters to a hyphen and the DOD field converts fine with no warning but when I try and change the DOB field which contains dates like where the year is 2000 and 2001 I get an overflow error.

Any ideas? :-( Frodo Baggins
Middle Earth, Hobbiton

 

[tlbroadbent]

Try adding the convert function.

Convert(datetime, Right([DOB],4) + '/' + Left([DOB],2) + '/' + SubString ([DOB],3,2), 111)

111 is style yyyy/mm/dd in SQL Server. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Vector01]

WIth the 111 style it converted to 'Oct 3 1919'. I tried other styles like 101 on the DOD and I still get convert string errors.

UPDATE ad1001 set ad1001.dod = Convert(datetime, Right([DOd],4) + '/' + Left([DOd],2) + '/' + SubString ([DOd],3,2), 111)

I'll keep trying. Thanks. Frodo Baggins
Middle Earth, Hobbiton

 

[tlbroadbent]

MMM DD YYYY is the default display. You need to use 111 to convert from the string to a date because your string or char field is in that format (yyyy/mm/dd). The date will be stored as a date (really an 8 byte number) regardless of the string format. Storage of dates is always in the same format. Only the display changes. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.