Date field mystery formatting

I have a field that is set as a datetime data type in my database.

The output from my query is correctly formatted.

When the scheduled job runs and outputs the data to a flat file, it adds a strange format:
2012-01-04 15:22:46.630000000

What could be causing this?  There isn't any special formatting being performed within my query.

Plus, not knowing the cause maked it difficult for me to resolve the issue.
programmherAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tim_csCommented:
It looks like your output is changing the field to a DATETIME2 data type.    
0
programmherAuthor Commented:
Datetime2 format?

Why?

How do I resolve this?

Why is it just doing that to that one datetime field and not all of them?
0
sureshbabukrishCommented:
why is it a problem for you? i think since this value does not change only the format which similar to normal date format only that the additional fractional seconds precision is present.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

programmherAuthor Commented:
This is a problem because the data flat file is being used for an edi tranmission and is causing errors.  The formst must be datetime instead of datetime2.
0
sureshbabukrishCommented:
can you please put some details on edi transmission ?
0
programmherAuthor Commented:
What kind of details do you need?  I will ask for whatever we do not have visibility to.

Also,thecompany with whomwe ar working is refusing to modify the edi side - the expectation istomake this"correction"on theSQL side.
0
programmherAuthor Commented:
The odd thing is, we have set the data type for these fields as datetime.  

How is datetime2 being selected or why?

Why aren't all the datetime fiields being reset as datetime2 datatypes upon output to the data file?
0
Scott PletcherSenior DBACommented:
When the scheduled job runs and outputs the data to a flat file
There isn't any special formatting being performed within my query.

If it's just a regular SQL query, try explicitly CASTing it to datetime yourself, to control the final data type:

SELECT ..., CAST(<existing_date_expression_in_query> AS datetime) AS colName, ...
FROM ...
0
programmherAuthor Commented:
I will try CASTing the field.

Using CONVERT resulted in an error when the job tried to output the result to the data file.
0
programmherAuthor Commented:
I tried using CAST and the output was still in datetime2 format.

Does anyone know why my datetime datatype is being ignored in the first place?
0
Scott PletcherSenior DBACommented:
Is this just a query in a job or is it in SSIS?

If it's in SSIS, you have to look at the format in SSIS also, not just the format in the query.
0
programmherAuthor Commented:
This is an SSIS package that a job executes every 30 minutes.

The package just outputs the records that are in a table.  The data type in the table for that field  is datetime.  I also included a CAST statement in the select statement and that didn't resolve the issue either.

Ideas?
0
Scott PletcherSenior DBACommented:
You have to look at the data mapping in the package.  Even when you don't see it, there's often an implicit data mapping going on in SSIS packages.  Sorry, since I can't see the package, I can't give you more details than that.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.