Data Type date ,Add function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Data Type date ,Add function

Author

Topic

poornima
Starting Member

32 Posts

Posted - 2006-10-24 : 04:08:00

Hello

I have an accounts table in which i have a date, credit,debit,balance
columns.I gave the datatype for date column as datetime.But after entering the values,Date is displayed in a different format.How to display the date in our specified format.

And also,in the balance column i want to store the balance.amd when ever there is a credit or debit,i want to add or subtract from the balance correspondingly.How do i manipulate this .

Thanks In Advance
Poornima

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-24 : 04:18:30

It does NOT matter which format the date is displayed in the EM or SSMS!
A date is really a decimal number where the integer part is the number of days passed since January 1, 1900. And the fraction is number of millisseconds passed since that midnight.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-24 : 04:24:15

1
Where do you want to show data?
If you use front end application, format the date there to the format you want and dont worry how dates are stored in table (provided you used DATETIME datatype)

2
You need to update that amout column accordingly

Madhivanan

Failing to plan is Planning to fail

poornima
Starting Member

32 Posts

Posted - 2006-10-24 : 04:33:01

Thanks
Can we not update the balance column by writing some expression like

balance = balance + credit
balance = balance + debit

like in MsExcel,where we insert a formula

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-24 : 04:39:54

Same post here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73953
And here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73956

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-24 : 05:05:19

[code]SELECT q.SNo,
CONVERT(varchar, q.Date, 102) Date,
q.Reason,
q.Credit,
q.Debit,
(SELECT SUM(x.Credit - x.Debit) FROM MyTable x WHERE x.Date <= q.Date) Balance
FROM MyTable q[/code]

Peter Larsson
Helsingborg, Sweden

raghuramgupta
Starting Member

3 Posts

Posted - 2006-10-25 : 03:17:36

select convert(varchar,datefield,109) from table

Use can use different type style types as mentioned below

Style ID Style Type
0 or 100 mon dd yyyy hh:miAM (or PM)
101 mm/dd/yy
102 yy.mm.dd
103 dd/mm/yy
104 dd.mm.yy
105 dd-mm-yy
106 dd mon yy
107 Mon dd, yy
108 hh:mm:ss
9 or 109 mon dd yyyy hh:mi:ss:mmmAM (or PM)
110 mm-dd-yy
111 yy/mm/dd
112 yymmdd
13 or 113 dd mon yyyy hh:mm:ss:mmm(24h)
114 hh:mi:ss:mmm(24h)
20 or 120 yyyy-mm-dd hh:mi:ss(24h)
21 or 121 yyyy-mm-dd hh:mi:ss.mmm(24h)
126 yyyy-mm-dd Thh:mm:ss.mmm
130 dd mon yyyy hh:mi:ss:mmmAM
131 dd/mm/yy hh:mi:ss:mmmAM

Raghuram Gupta.Sarabu
GGK Tech
Hyderabad
India

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources