February 24, 2011 at 8:28 am
I want to say firstly that I did post this in SSIS but I know any 80% of developers have had to use the SSIS Data Task flow only to be cursed at everywhich way from sunday from SSIS. I do not want to double post but did not see how I could delete the same post in the BI forum.
I also feel it is ultimatley going to be answer I need for regular T-sql scripting so this would be the right forum. Please try and help instead of telling me I should post this in BI :-).
source table I am using an SQL command (query text) I have:
[FILE_RUN_DATE] [varchar](8) NULL,
in destination it is mappied to this column:
[FileRunDate] [datetime] NULL,
I am trying this in the source query NOT using the SSIS Data Converter:
convert(datetime,[FILE_RUN_DATE],112) AS 'FILE_RUN_DATE'
and no longer getting the truncate but data conversion problem.
a. I can preferrebly convert it in the SQL command of source query, what data type of date is 8 characters? Am I going crazy or is there no date data type with 8 characters excluding seconds? smalldatetime has seconds but just another range. the destination table does not like anothing but a full datetime with seconds included. Is there a way to concactenate the seconds like
convert(datetime,[FILE_RUN_DATE],112) + 00:00:000 AS 'FILE_RUN_DATE'
b. If I use ssis data converter what data type do I use for date that is date but only 8 charactes. I thought there was a datatype for just date and not time?
Thanks in advance!
March 1, 2011 at 8:20 am
SQL Server 2005 only has the datetime field type. A date type field was added in SQL 2008.
Your conversion should give you a date with all zeros for the time. That's as close as you can get.
As for a conversion error, whats the format of your FILE_RUN_DATE field?
If it is MM/DD/YYYY then you should be using 101 instead of 112 in your CONVERT function call.
--------------------------------------------------------------------------
When you realize you've dug yourself into a hole....Step 1...stop digging.
March 2, 2011 at 4:37 am
Hello,
your CONVERT sentence looks right, I tried this,
WITH X AS (SELECT '20110101' AS FILE_RUN_DATE
UNION SELECT '20110202'
UNION SELECT '20110303'
UNION SELECT '20110404')
SELECT convert(datetime,[FILE_RUN_DATE],112) AS 'FILE_RUN_DATE' FROM X
and it worked well. When adding another date in a different format,
WITH X AS (SELECT '20110101' AS FILE_RUN_DATE
UNION SELECT '20110202'
UNION SELECT '20110303'
UNION SELECT '20110404'
UNION SELECT '05052011')
SELECT convert(datetime,[FILE_RUN_DATE],112) AS 'FILE_RUN_DATE' FROM X
I obtained a conversion error,
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
so I suppose the problem is in your date format. Maybe your selection of the 112 format is incorrect, or maybe you have mixed format dates in your data.
Hope this helps,
Francesc
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply