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?
 
Scott PletcherConnect With a Mentor Senior 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
 
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.