Default Date Format used by Mssql

Hi there,

What is the default date format used by transact sql? Would YYYY-MM-DD HOUR:MIN:SECS format work on Mssql?

I am working on a project that needs to work with atleast two databases (Mysql/Mssql). I use the above date format and while it works perfectly on all Mysql databases, it gives me trouble in some Mssql setups.

Most of the trouble arises when I am doing INSERT or SELECT queries.

How do I handle this? Is there some way that I can tell Mssql that I am using the yyyy-mm-dd format or should I find out what format that particular mssql is using and adopt it?YYYY-MM-DD hh:mm:ss should work fine with mssql. that's basically the ODBC format.

See http://msdn2.microsoft.com/en-us/library/ms187928.aspx for a list of all the different date formats in sql server.|||I've never had the ISO standard temporal format (YYYY-MM-DD HH:MM:SS.TTT) give me trouble with Microsoft SQL, that is actually the preferred format for dates and times. The only thing I've had trouble with Microsoft-SQL handling DATETIME values in that time format was because MS-SQL can only resolve time down to 3 ms so it sees all three of the following times as identical:

2006-08-07 06:05:04.000
2006-08-07 06:05:04.001
2006-08-07 06:05:04.002

If you are using SMALLDATETIME values, things get more interesting quickly, since those are only accurate to the minute. That might be a whole different issue.

-PatP|||One of my clients use Mssql and her server uses the YYYY-DD-MM date format. I had to change the date formatting in the code to make it work for her.

I would really need to know whether this is an issue in some mssql servers.|||No, I don't know of ANY condition under which MS-SQL 7.0 or later versions have any problem interpreting dates formatted as YYYY-MM-DD as long as the date is valid for the datatype you are using.

As I don't know what made you think you needed to change the date formatting, all I can do is say that the date format isn't the problem, something else is.

-PatP|||Yeah, When I changed my date format, the queries worked. So, the problem is really with the format.|||Maybe I'm not making this clear, but the ISO string format you are using for the date is NOT the problem. I don't know what is the problem, but the ISO format isn't it.

-PatP

Decoding a binary base64 inside a XML using transact SQL

Yes i just can't find a place to help me out on something.

Everything went well to create the XML using transact SQL command

SELECT TOP 10 * FROM TblEvenement for xml auto, binary base64

But how do decode my image field in my XML so i can save the data into a new table ..

all this as to be done in a store procedure inside SQL-Server 2000 !!

thanx guys !!

***Any good sites or example i can use for !!What i did is a store proc in SQL-Server 2000 that build an XML document so i can transfert data between my two server.

But i have image fields and i would like to transfert that as well. I can encode it no problem ( for xml auto, binary base64 ), but how can i DECODE my TEXT value (int the XML document) using Transact SQL to store my image to the new server !!

thanx|||Did you find any solution to this. I'm really interested.|||¸Yes i build a function in my SQL DataBase that convert my image but works only with images that have less or equal to binary(8000)..

but im looking for something else.. its not working as i would like so.. im keep working on it.

still need help guys !!!

not a single site in the web that have an example..

there must be a way !! or its to easy so no buddy wants to help me out..|||Does this post help you?OPENXML and image binary base64.

The approach here is to create a UDF which will convert base64 to binary, and use this function with OPENXML.

Terri|||I try the function but the problem is that it return a varbinary(8000) .. its to short.. all my images are over 10K

so im trying something else.. must be a way

thanx

DECIMAL with Scale 2 truncation of SSIS Differs from Transact SQL

Hi,
I have a derived column transformation which adds a new column of type Decimal with scale 2.

The expression is (6800 / 464)

Runs fine but it returns

14.65

While if I run any of the following queries in query analyzer

SELECT 6800 / 464

OR

SELECT CONVERT(DECIMAL(10,2), 6800 / 464)

They give me 14.66.

My question is, why both the tool of same product differs in the way they work ? and How could I have SSIS to work like TSQL ? I tried typecasting in derived column expression and ROUND function too. But still, the output is same.

Any help in this will sincerely be appreciated.

Thanks

How are you dong this calculation in SSIS and how are you visualising the result?

I did a quick test in the editor for a property expression and got 14.66, (DT_WSTR,10)(ROUND(6800.0/ 464.0,2))

|||

DarrenSQLIS wrote:

How are you dong this calculation in SSIS and how are you visualising the result?

I did a quick test in the editor for a property expression and got 14.66, (DT_WSTR,10)(ROUND(6800.0/ 464.0,2))

But the intuitive way doesn't work: (both report 14.65)

(DT_NUMERIC,6,2)((DT_NUMERIC,6,2)6800 / (DT_NUMERIC,6,2)464)
(DT_NUMERIC,6,2)6800 / (DT_NUMERIC,6,2)464|||I don't think casting inputs too small is intuitive, and the lack of a third decimal place is the whole problem. I think you should always apply formatting last, which is what the rounding or truncation is to me.|||

DarrenSQLIS wrote:

I don't think casting inputs too small is intuitive, and the lack of a third decimal place is the whole problem. I think you should always apply formatting last, which is what the rounding or truncation is to me.

According to this, http://msdn2.microsoft.com/en-us/library/ms187928.aspx, going from numeric to numeric is supposed to round not truncate.

The only reason I say that casting the inputs is because in this example, they would be considered integers, not numerics.

Never-the-less, a developer should be able to simply write 3948/38 in an expression and set the output to numeric with a precision and a scale, and end up with a correct result. A developer shouldn't have to worry about casting inputs, using the round() function, etc...

|||

Phil Brammer wrote:

According to this, http://msdn2.microsoft.com/en-us/library/ms187928.aspx, going from numeric to numeric is supposed to round not truncate.

It is important to note that the SSIS expression language follows C-style rules, not T-SQL. Behaviour in this case is a little different.

Phil Brammer wrote:

Never-the-less, a developer should be able to simply write 3948/38 in an expression and set the output to numeric with a precision and a scale, and end up with a correct result. A developer shouldn't have to worry about casting inputs, using the round() function, etc...

In the case of the SSIS expression language, there should indeed be no difference between

(DT_NUMERIC,4,2)3948/38

and

(DT_NUMERIC,4,2)3948/(DT_NUMERIC,4,2)38

since for binary operators, the operands will be implicitly cast to an appropriate result type before the operation is performed.

Thanks
Mark

|||Thankyou so much for your effort guys.

The problem was, SSIS Derived Column Trasnformation was truncating while TSQL was rounding.

So what I did is, I would force SSIS to compute higher precision value and then ROUND it to the precision I want.

so

ROUND( ((DT_DECIMAL,4)6800) / ((DT_NUMERIC, 4) 464) , 2)

and it returned me the 14.66 which matched the result of TSQL.

Thanks once again for your inputs which helped me

DECIMAL with Scale 2 truncation of SSIS Differs from Transact SQL

Hi,
I have a derived column transformation which adds a new column of type Decimal with scale 2.

The expression is (6800 / 464)

Runs fine but it returns

14.65

While if I run any of the following queries in query analyzer

SELECT 6800/ 464

OR

SELECTCONVERT(DECIMAL(10,2), 6800/ 464)

They give me 14.66.

My question is, why both the tool of same product differs in the way they work ? and How could I have SSIS to work like TSQL ? I tried typecasting in derived column expression and ROUND function too. But still, the output is same.

Any help in this will sincerely be appreciated.

Thanks

How are you dong this calculation in SSIS and how are you visualising the result?

I did a quick test in the editor for a property expression and got 14.66, (DT_WSTR,10)(ROUND(6800.0/ 464.0,2))

|||

DarrenSQLIS wrote:

How are you dong this calculation in SSIS and how are you visualising the result?

I did a quick test in the editor for a property expression and got 14.66, (DT_WSTR,10)(ROUND(6800.0/ 464.0,2))

But the intuitive way doesn't work: (both report 14.65)

(DT_NUMERIC,6,2)((DT_NUMERIC,6,2)6800 / (DT_NUMERIC,6,2)464)
(DT_NUMERIC,6,2)6800 / (DT_NUMERIC,6,2)464|||I don't think casting inputs too small is intuitive, and the lack of a third decimal place is the whole problem. I think you should always apply formatting last, which is what the rounding or truncation is to me.|||

DarrenSQLIS wrote:

I don't think casting inputs too small is intuitive, and the lack of a third decimal place is the whole problem. I think you should always apply formatting last, which is what the rounding or truncation is to me.

According to this, http://msdn2.microsoft.com/en-us/library/ms187928.aspx, going from numeric to numeric is supposed to round not truncate.

The only reason I say that casting the inputs is because in this example, they would be considered integers, not numerics.

Never-the-less, a developer should be able to simply write 3948/38 in an expression and set the output to numeric with a precision and a scale, and end up with a correct result. A developer shouldn't have to worry about casting inputs, using the round() function, etc...

|||

Phil Brammer wrote:

According to this, http://msdn2.microsoft.com/en-us/library/ms187928.aspx, going from numeric to numeric is supposed to round not truncate.

It is important to note that the SSIS expression language follows C-style rules, not T-SQL. Behaviour in this case is a little different.

Phil Brammer wrote:

Never-the-less, a developer should be able to simply write 3948/38 in an expression and set the output to numeric with a precision and a scale, and end up with a correct result. A developer shouldn't have to worry about casting inputs, using the round() function, etc...

In the case of the SSIS expression language, there should indeed be no difference between

(DT_NUMERIC,4,2)3948/38

and

(DT_NUMERIC,4,2)3948/(DT_NUMERIC,4,2)38

since for binary operators, the operands will be implicitly cast to an appropriate result type before the operation is performed.

Thanks
Mark

|||Thankyou so much for your effort guys.

The problem was, SSIS Derived Column Trasnformation was truncating while TSQL was rounding.

So what I did is, I would force SSIS to compute higher precision value and then ROUND it to the precision I want.

so

ROUND( ((DT_DECIMAL,4)6800) / ((DT_NUMERIC, 4) 464) , 2)

and it returned me the 14.66 which matched the result of TSQL.

Thanks once again for your inputs which helped me

DECIMAL with Scale 2 truncation of SSIS Differs from Transact SQL

Hi,
I have a derived column transformation which adds a new column of type Decimal with scale 2.

The expression is (6800 / 464)

Runs fine but it returns

14.65

While if I run any of the following queries in query analyzer

SELECT 6800 /

464

OR

SELECT CONVERT(DECIMAL(10,2), 6800 / 464)

They give me 14.66.

My question is, why both the tool of same product differs in the way they work ? and How could I have SSIS to work like TSQL ? I tried typecasting in derived column expression and ROUND function too. But still, the output is same.

Any help in this will sincerely be appreciated.

Thanks

How are you dong this calculation in SSIS and how are you visualising the result?

I did a quick test in the editor for a property expression and got 14.66, (DT_WSTR,10)(ROUND(6800.0/ 464.0,2))

|||

DarrenSQLIS wrote:

How are you dong this calculation in SSIS and how are you visualising the result?

I did a quick test in the editor for a property expression and got 14.66, (DT_WSTR,10)(ROUND(6800.0/ 464.0,2))

But the intuitive way doesn't work: (both report 14.65)

(DT_NUMERIC,6,2)((DT_NUMERIC,6,2)6800 / (DT_NUMERIC,6,2)464)
(DT_NUMERIC,6,2)6800 / (DT_NUMERIC,6,2)464|||I don't think casting inputs too small is intuitive, and the lack of a third decimal place is the whole problem. I think you should always apply formatting last, which is what the rounding or truncation is to me.|||

DarrenSQLIS wrote:

I don't think casting inputs too small is intuitive, and the lack of a third decimal place is the whole problem. I think you should always apply formatting last, which is what the rounding or truncation is to me.

According to this, http://msdn2.microsoft.com/en-us/library/ms187928.aspx, going from numeric to numeric is supposed to round not truncate.

The only reason I say that casting the inputs is because in this example, they would be considered integers, not numerics.

Never-the-less, a developer should be able to simply write 3948/38 in an expression and set the output to numeric with a precision and a scale, and end up with a correct result. A developer shouldn't have to worry about casting inputs, using the round() function, etc...

|||

Phil Brammer wrote:

According to this, http://msdn2.microsoft.com/en-us/library/ms187928.aspx, going from numeric to numeric is supposed to round not truncate.

It is important to note that the SSIS expression language follows C-style rules, not T-SQL. Behaviour in this case is a little different.

Phil Brammer wrote:

Never-the-less, a developer should be able to simply write 3948/38 in an expression and set the output to numeric with a precision and a scale, and end up with a correct result. A developer shouldn't have to worry about casting inputs, using the round() function, etc...

In the case of the SSIS expression language, there should indeed be no difference between

(DT_NUMERIC,4,2)3948/38

and

(DT_NUMERIC,4,2)3948/(DT_NUMERIC,4,2)38

since for binary operators, the operands will be implicitly cast to an appropriate result type before the operation is performed.

Thanks
Mark

|||Thankyou so much for your effort guys.

The problem was, SSIS Derived Column Trasnformation was truncating while TSQL was rounding.

So what I did is, I would force SSIS to compute higher precision value and then ROUND it to the precision I want.

so

ROUND( ((DT_DECIMAL,4)6800) / ((DT_NUMERIC, 4) 464) , 2)

and it returned me the 14.66 which matched the result of TSQL.

Thanks once again for your inputs which helped me

Decimal places for integer divisions

SELECT 1/2 returns 0 in transact sql instead of 0.500000
How do I get it to return 0.5000 ?You need to force SQL Server to not do integer division. Try:

SELECT CAST(1 AS float) / CAST(2 AS float)|||You really have to understand datatypes. I still regularly get questions that resemble this one from developers who've been using SQL for years.

In integer division, one divided by two equals zero with a remainder of one. When you divide one integer by another, integer math is used. If you cast either the dividend or the divisor to a real, then real math will be used (with implicit promotion if needed).

-PatP|||Originally posted by calicocat
You need to force SQL Server to not do integer division. Try:

SELECT CAST(1 AS float) / CAST(2 AS float)

Nah...it'll infer type if you let it...

SELECT 1/2.0|||A little more on my problem.

I have two columns in a table each storing integer values. I would like the division to return a decimal number

eg. select col1/col2 should return a decimal result... Neither of the above solutions work.|||OK, so now you need to convert

USE Northwind
GO

CREATE TABLE myTable99(Col1 int, Col2 int)
GO

INSERT INTO myTable99(Col1,Col2)
SELECT 1,2
GO

SELECT Col1/CONVERT(decimal(8,4),Col2)
FROM myTable99
GO

DROP TABLE myTable99
GO