CODEWITHSUNDEEP
sql-serverformulacalculated-columns
Mehrdad
Mehrdad

Reputation: 2116

Computed Column Formula

I've created a computed column with this formula:

(CONVERT([datetime],(CONVERT([varchar],[TradeDate])+' ')+CONVERT([varchar],[TradeTime])))

but when I save the table, the formula automatically changes to: 

(CONVERT([datetime],(CONVERT([varchar],[TradeDate],0)+' ')+CONVERT([varchar],[TradeTime],0),0))

So it causes this problem:

Suppose TradeDate is: 2012-08-13 and TradeTime is: 10:29:59.0000000

and instead of showing 2012-08-13 10:29:59.000, it shows 2012-08-13 10:29:00.000, because of changing the formula.

What's the solution?

Upvotes: 0

Views: 1485

Answers (2)

praveen
praveen

Reputation: 12271

SQL Server adds 0 to your convert expression because that is the default style which represents Datetime in the format mon dd yyyy hh:miAM (or PM)

The 3rd parameter in your convert function represents style.So 0 or 100 represents default style .So u can specify the style when creating the computed column .

CAST and Convert

Upvotes: 0

bummi
bummi

Reputation: 27367

Just use (CONVERT([datetime],[TradeDate],0)+CONVERT([datetime],[TradeTime],0))

Upvotes: 1

Related Questions