June 16, 2010 at 4:15 am
I’m reading data from flat files and storing it in staging table in SQL Server 2008.
The destination table, say Date_tbl contains date_col with datatype as “date”
My query looks like.
Insert into date_tbl (date_col)
Select (SUBSTRING(code,87,8)) from staging_table.
My staging table has this data stored as varchar(108).
The value for (SUBSTRING(code,87,8)) = 19031986
When I try inserting data in date_tbl I get error like:
Conversion failed when converting date and/or time from character string.
I have also tried setting dateformat to dmy and
SUBSTRING((SUBSTRING(code,87,8)),1,2) + '/' + SUBSTRING((SUBSTRING(code,87,8)),3,2) + '/' +
SUBSTRING((SUBSTRING(code,87,8)),5,4)
But no help.
Can someone help me to populate the date_tbl with correct date.
June 16, 2010 at 4:50 am
Build the date using substring as you have done , but to the format YYYYMMDD and you will be fine.
June 16, 2010 at 4:52 am
Yes, have done the same way right after posting the query.
But somehow I find it tedious and incorrect.
Is there any other way I can acheive this.
June 16, 2010 at 5:07 am
Cast and Convert can use a series of known formats
http://msdn.microsoft.com/en-us/library/ms187928.aspx
However DDMMYYYY is not one of them , it would be nice if you could provide a User Defined format, but you cant.
--EDIT
You could use a view or a persisted column to hold the conversion it you want to 'hide' it.
June 17, 2010 at 4:50 am
Hi,
Is there any other way I can validate my column if it contains valid date.
I have been using ISDATE() but my column has data like dmy (19031986)
so for this value also it shows as invalid date.
Is there any other way without me converting it to (1986/03/19) and then doing ISDATE()
Thanks
Khushbu
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply