August 29, 2012 at 1:31 pm
Hi All,
I need to export some data from SQL Server (2008 R2) table to a text file. There are a few datatime data type columns in the sql table.
Here's the truncated table definition and some sample data from the table.
Table definition:
Create table test
col1 datetime
.
.
.
Sample data for col1 column
---------------------------
2012-05-20 00:00:00:000
2012-05-20 12:12:12:245
2011-04-20 23:11:12:000
2010-07-05 01:07:09:078
.
.
.
I need to export all the data from the test table to a text file in such a way that the milliseconds in the text file for col1 is 6 digits. So for example, the col1 in the sql table is stored as 2012-05-20 00:00:00:000 and it should be 2012-05-20 00:00:00:000000 in text file, 2012-05-20 12:12:12:245 in sql table should be stored as
2012-05-20 12:12:12:245000 in the text file etc. Please help.
August 29, 2012 at 1:42 pm
suman.mullapudi (8/29/2012)
Hi All,I need to export some data from SQL Server (2008 R2) table to a text file. There are a few datatime data type columns in the sql table.
Here's the truncated table definition and some sample data from the table.
Table definition:
Create table test
col1 datetime
.
.
.
Sample data for col1 column
---------------------------
2012-05-20 00:00:00:000
2012-05-20 12:12:12:245
2011-04-20 23:11:12:000
2010-07-05 01:07:09:078
.
.
.
I need to export all the data from the test table to a text file in such a way that the milliseconds in the text file for col1 is 6 digits. So for example, the col1 in the sql table is stored as 2012-05-20 00:00:00:000 and it should be 2012-05-20 00:00:00:000000 in text file, 2012-05-20 12:12:12:245 in sql table should be stored as
2012-05-20 12:12:12:245000 in the text file etc. Please help.
That is a rather strange requirement but it is pretty simple to do. Look up CONVERT in BOL. http://msdn.microsoft.com/en-us/library/ms187928.aspx
All you need to do is find the style you want (probably 21 based on your description) and concatenate your extra 0's to it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 29, 2012 at 1:42 pm
Something like this?
SELECT CONVERT( varchar(23), GETDATE(), 121)+'000'
If its datetime2 instead of datetime, you can use varchar(26) with no concatenation.
August 29, 2012 at 1:51 pm
Thanks. Sorry for not being clear in my earlier post. I need to export data for all the columns and there are about 65 columns in the table, out of them there are about 12 datetime columns. I need to format all the datetime columns (milliseconds should be 6 digits as described above) and I need to format other columns as well. I am having issues while transforming the datetime data type column values, I am using SSIS to do this task. Can you let me know how I can do this in SSIS?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply