SQL Syntax convert string to date

This should be easy, but just not working...How does one change an 8 character string 20100204 to a date using either sql syntax or SSIS - tried the DAta conversion but it fails,,,and SQL gives me something funky Dec 9 2 no matter what i use as an arg on the convert function...
Gray5452Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
kumarnimavatConnect With a Mentor Commented:
You may try selecting by using the below given query
select convert(datetime, '20100204')

I tried updating a datetime field using the query given below which worked fine. You may give it a try.
update Table_1 set test = convert(datetime, '20100204')
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
CONVERT(datetime, yourfield, 112)

http://msdn.microsoft.com/en-us/library/ms187928.aspx
0
 
Gray5452Author Commented:
it looks correct when I do it in a select but when I run the update the dates come out looking like Nov 13 2
0
 
SharathData EngineerCommented:
try this.
CONVERT(varchar,CONVERT(datetime,date_col,112),101)

Open in new window

check this example
declare @col varchar(20) = '20100204'
select @col = CONVERT(varchar,CONVERT(datetime,@col,112),101)
select @col -- 02/04/2010

Open in new window

0
 
BlakeRogersCommented:
Try setting it as a variable first and seeing if that works with Sharaths CONVERT.

The other option is to do a CAST

CAST('2007-05-08 12:35:29.123' AS datetime) AS 'datetime' 

Open in new window

0
All Courses

From novice to tech pro — start learning today.