Solved

Exporting data with sql-dmo (dates issue)

Posted on 2002-05-05
4
530 Views
Last Modified: 2009-07-29
Hi,

I wrote small vb app to export tables via BCP with sql-dmo.
Everythings works fine but... I have few dates column which are represented in the exported file as
{ ts '1990-....'} . How do i change the format for a regular one .  when i use the bcp command from a dos prompt the date format is ok.   any ideas?
0
Comment
Question by:nirkr
4 Comments
 
LVL 35

Accepted Solution

by:
David Todd earned 200 total points
Comment Utility
Hi,

In SQL try converting to char.

eg convert( datecolumn, char(20), 120 )

The last number is the style.

BOL

 Transact-SQL Reference

 
CAST and CONVERT
Explicitly converts an expression of one data type to another. CAST and CONVERT provide similar functionality.

Syntax
Using CAST:

CAST ( expression AS data_type )

Using CONVERT:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Arguments
expression

Is any valid Microsoft. SQL Server expression. For more information, see Expressions.

data_type

Is the target system-supplied data type, including bigint and sql_variant. User-defined data types cannot be used. For more information about available data types, see Data Types.

length

Is an optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types.

style

Is the style of date format used to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or the string format when converting float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).

SQL Server supports the date format in Arabic style, using Kuwaiti algorithm.

In the table, the two columns on the left represent the style values for datetime or smalldatetime conversion to character data. Add 100 to a style value to get a four-place year that includes the century (yyyy).

Without century (yy) With century (yyyy)
Standard
Input/Output**
- 0 or 100 (*)  Default mon dd yyyy hh:miAM (or PM)
1 101 USA mm/dd/yy
2 102 ANSI yy.mm.dd
3 103 British/French dd/mm/yy
4 104 German dd.mm.yy
5 105 Italian dd-mm-yy
6 106 - dd mon yy
7 107 - Mon dd, yy
8 108 - hh:mm:ss
- 9 or 109 (*)  Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)
10 110 USA mm-dd-yy
11 111 JAPAN yy/mm/dd
12 112 ISO yymmdd
- 13 or 113 (*)  Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h)
14 114 - hh:mi:ss:mmm(24h)
- 20 or 120 (*)  ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
- 21 or 121 (*)  ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)
- 126(***) ISO8601 yyyy-mm-dd Thh:mm:ss:mmm(no spaces)
- 130* Kuwaiti dd mon yyyy hh:mi:ss:mmmAM
- 131* Kuwaiti dd/mm/yy hh:mi:ss:mmmAM


*    The default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the century (yyyy).
** Input when converting to datetime; output when converting to character data.
*** Designed for XML use. For conversion from datetime or smalldatetime to character data, the output format is as described in the table. For conversion from float, money, or smallmoney to character data, the output is equivalent to style 2. For conversion from real to character data, the output is equivalent to style 1.



Important  By default, SQL Server interprets two-digit years based on a cutoff year of 2049. That is, the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950. Many client applications, such as those based on OLE Automation objects, use a cutoff year of 2030. SQL Server provides a configuration option (two digit year cutoff) that changes the cutoff year used by SQL Server and allows the consistent treatment of dates. The safest course, however, is to specify four-digit years.


When you convert to character data from smalldatetime, the styles that include seconds or milliseconds show zeros in these positions. You can truncate unwanted date parts when converting from datetime or smalldatetime values by using an appropriate char or varchar data type length.

This table shows the style values for float or real conversion to character data.

Value Output
0 (default) Six digits maximum. Use in scientific notation, when appropriate.
1 Always eight digits. Always use in scientific notation.
2 Always 16 digits. Always use in scientific notation.


In the following table, the column on the left represents the style value for money or smallmoney conversion to character data.

Value Output
0 (default) No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 4235.98.
1 Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92.
2 No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point; for example, 4235.9819.  


Return Types
Returns the same value as data type 0.

Remarks
Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function. Explicit conversions are those conversions that require the CAST (CONVERT) function to be specified. This chart shows all explicit and implicit data type conversions allowed for SQL Server system-supplied data types, including bigint and sql_variant.





Note  Because Unicode data always uses an even number of bytes, use caution when converting binary or varbinary to or from Unicode supported data types. For example, this conversion does not return a hexadecimal value of 41, but of 4100:  SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary)


Automatic data type conversion is not supported for the text and image data types. You can explicitly convert text data to character data, and image data to binary or varbinary, but the maximum length is 8000. If you attempt an incorrect conversion (for example, if you convert a character expression that includes letters to an int), SQL Server generates an error message.

When the output of CAST or CONVERT is a character string, and the input is a character string, the output has the same collation and collation label as the input. If the input is not a character string, the output has the default collation of the database, and a collation label of coercible-default. For more information, see Collation Precedence.

To assign a different collation to the output, apply the COLLATE clause to the result expression of the CAST or CONVERT function. For example:

SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS
There is no implicit conversion on assignment from the sql_variant data type but there is implicit conversion to sql_variant.

When converting character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display. Conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated, except for the conversions shown in this table.

From data type To data type Result
int, smallint, or tinyint char *
  varchar *
  nchar E
  nvarchar E
money, smallmoney, numeric, decimal, float, or real char E
  varchar E
  nchar E
  nvarchar E


* Result length too short to display.
E Error returned because result length is too short to display.

Microsoft SQL Server guarantees that only roundtrip conversions, conversions that convert a data type from its original data type and back again, will yield the same values from release to release. This example shows such a roundtrip conversion:

DECLARE @myval decimal (5, 2)
SET @myval = 193.57
SELECT CAST(CAST(@myval AS varbinary(20)) AS decimal(10,5))
-- Or, using CONVERT
SELECT CONVERT(decimal(10,5), CONVERT(varbinary(20), @myval))
Do not attempt to construct, for example, binary values and convert them to a data type of the numeric data type category. SQL Server does not guarantee that the result of a decimal or numeric data type conversion to binary will be the same between releases of SQL Server.

This example shows a resulting expression too small to display.

USE pubs
SELECT SUBSTRING(title, 1, 25) AS Title, CAST(ytd_sales AS char(2))
FROM titles
WHERE type = 'trad_cook'
Here is the result set:

Title                        
------------------------- --
Onions, Leeks, and Garlic *  
Fifty Years in Buckingham *  
Sushi, Anyone?            *  

(3 row(s) affected)
When data types are converted with a different number of decimal places, the value is truncated to the most precise digit. For example, the result of SELECT CAST(10.6496 AS int) is 10.

When data types in which the target data type has fewer decimal points than the source data type are converted, the value is rounded. For example, the result of CAST(10.3496847 AS money) is $10.3497.

SQL Server returns an error message when non-numeric char, nchar, varchar, or nvarchar data is converted to int, float, numeric, or decimal. SQL Server also returns an error when an empty string (" ") is converted to numeric or decimal.

Using Binary String Data
When binary or varbinary data is converted to character data and an odd number of values is specified following the x, SQL Server adds a 0 (zero) after the x to make an even number of values.

Binary data consists of the characters from 0 through 9 and from A through F (or from a through f), in groups of two characters each. Binary strings must be preceded by 0x. For example, to input FF, type 0xFF. The maximum value is a binary value of 8000 bytes, each of which is FF. The binary data types are not for hexadecimal data but rather for bit patterns. Conversions and calculations of hexadecimal numbers stored as binary data can be unreliable.

When specifying the length of a binary data type, every two characters count as one. A length of 10 signifies that 10 two-character groupings will be entered.

Empty binary strings, represented by 0x, can be stored as binary data.

Examples
A. Use both CAST and CONVERT
Each example retrieves the titles for those books that have a 3 in the first digit of year-to-date sales, and converts their ytd_sales to char(20).

-- Use CAST.
USE pubs
GO
SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales
FROM titles
WHERE CAST(ytd_sales AS char(20)) LIKE '3%'
GO

-- Use CONVERT.
USE pubs
GO
SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales
FROM titles
WHERE CONVERT(char(20), ytd_sales) LIKE '3%'
GO
Here is the result set (for either query):

Title                          ytd_sales  
------------------------------ -----------
Cooking with Computers: Surrep 3876        
Computer Phobic AND Non-Phobic 375        
Emotional Security: A New Algo 3336        
Onions, Leeks, and Garlic: Coo 375        

(4 row(s) affected)
B. Use CAST with arithmetic operators
This example calculates a single column computation (Copies) by dividing the total year-to-date sales (ytd_sales) by the individual book price (price). This result is converted to an int data type after being rounded to the nearest whole number.

USE pubs
GO
SELECT CAST(ROUND(ytd_sales/price, 0) AS int) AS 'Copies'
FROM titles
GO
Here is the result set:

Copies      
------
205        
324        
6262        
205        
102        
7440        
NULL        
383        
205        
NULL        
17          
187        
16          
204        
418        
18          
1263        
273        

(18 row(s) affected)
C. Use CAST to concatenate
This example concatenates noncharacter, nonbinary expressions using the CAST data type conversion function.

USE pubs
GO
SELECT 'The price is ' + CAST(price AS varchar(12))
FROM titles
WHERE price > 10.00
GO
Here is the result set:

------------------
The price is 19.99        
The price is 11.95        
The price is 19.99        
The price is 19.99        
The price is 22.95        
The price is 20.00        
The price is 21.59        
The price is 10.95        
The price is 19.99        
The price is 20.95        
The price is 11.95        
The price is 14.99        

(12 row(s) affected)
D. Use CAST for more readable text
This example uses CAST in the select list to convert the title column to a char(50) column so the results are more readable.

USE pubs
GO
SELECT CAST(title AS char(50)), ytd_sales
FROM titles
WHERE type = 'trad_cook'
GO
Here is the result set:

                                                       ytd_sales
--------------------------------------------------     ---------
Onions, Leeks, and Garlic: Cooking Secrets of the      375
Fifty Years in Buckingham Palace Kitchens              15096
Sushi, Anyone?                                         4095

(3 row(s) affected)
E. Use CAST with LIKE clause
This example converts an int column (the ytd_sales column) to a char(20) column so that it can be used with the LIKE clause.

USE pubs
GO
SELECT title, ytd_sales
FROM titles
WHERE CAST(ytd_sales AS char(20)) LIKE '15%'
   AND type = 'trad_cook'
GO
Here is the result set:

title                                                        ytd_sales  
------------------------------------------------------------ -----------
Fifty Years in Buckingham Palace Kitchens                    15096      

(1 row(s) affected)

See Also

Data Type Conversion

SELECT

System Functions

)1988-2000 Microsoft Corporation. All Rights Reserved.

Regards
  David
0
 
LVL 1

Expert Comment

by:Moondancer
Comment Utility
ADMINISTRATION WILL BE CONTACTING YOU SHORTLY.  Moderators Computer101, Netminder or Mindphaser will return to finalize these if they are still open in 7 days.  Experts, please post closing recommendations before that time.

Below are your open questions as of today.  Questions which have been inactive for 21 days or longer are considered to be abandoned and for those, your options are:
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you, but may help others. You must tell the participants why you wish to do this, and allow for Expert response.  This choice will include a refund to you, and will move this question to our PAQ (Previously Asked Question) database.  If you found information outside this question thread, please add it.
3. Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4. Delete the question (if it has no potential value for others).
   --> Post comments for expert of your intention to delete and why
   --> YOU CANNOT DELETE A QUESTION with comments; special handling by a Moderator is required.

For special handling needs, please post a zero point question in the link below and include the URL (question QID/link) that it regards with details.
http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
 
Please click this link for Help Desk, Guidelines/Member Agreement and the Question/Answer process.  http://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp

Click you Member Profile to view your question history and please keep them updated. If you are a KnowledgePro user, use the Power Search option to find them.  

Questions which are LOCKED with a Proposed Answer but do not help you, should be rejected with comments added.  When you grade the question less than an A, please comment as to why.  This helps all involved, as well as others who may access this item in the future.  PLEASE DO NOT AWARD POINTS TO ME.

To view your open questions, please click the following link(s) and keep them all current with updates.
http://www.experts-exchange.com/questions/Q.12012039.html
http://www.experts-exchange.com/questions/Q.20060786.html
http://www.experts-exchange.com/questions/Q.20243833.html
http://www.experts-exchange.com/questions/Q.20074049.html
http://www.experts-exchange.com/questions/Q.20123691.html
http://www.experts-exchange.com/questions/Q.20144936.html
http://www.experts-exchange.com/questions/Q.20226250.html
http://www.experts-exchange.com/questions/Q.20297148.html
http://www.experts-exchange.com/questions/Q.20297149.html

To view your locked questions, please click the following link(s) and evaluate the proposed answer.
http://www.experts-exchange.com/questions/Q.20167106.html

*****  E X P E R T S    P L E A S E  ******  Leave your closing recommendations.
If you are interested in the cleanup effort, please click this link
http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20274643
POINTS FOR EXPERTS awaiting comments are listed in the link below
http://www.experts-exchange.com/commspt/Q.20277028.html
 
Moderators will finalize this question if in @7 days Asker has not responded.  This will be moved to the PAQ (Previously Asked Questions) at zero points, deleted or awarded.
 
Thanks everyone.
Moondancer
Moderator @ Experts Exchange
0
 

Expert Comment

by:CleanupPing
Comment Utility
nirkr:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now