The objective is displaying day of month in SQL Server 2005.
In Oracle it is done by using rownum within the script below:
1: Select Rownum
2: From all_objects Where Rownum Between 1
3: And To_char(Last_day(To_date('&pdate', 'MM')), 'DD');
1: Enter value for pdate: 03
ROWNUM
------ 12
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
ows selected.
What approach I took in SQL Server to return the same out?
- As a matter of fact, there is no such rownum in SQL Server, instead I utilizee “top”,
- There is no all_objects in SQL Server at least, instead I used msdb.dbo.MSdbms_map table,
- SO I used the table as a fake reference to repeat as many times as datediff did.
So the query is:
1: SELECT RIGHT('00' + CONVERT(VARCHAR(2), Kldr.tgl), 2) Tgl2
2: FROM (SELECT TOP (DATEDIFF
3: (day, dateadd
4: (m, 0, dateadd 5: (d, ((datepart 6: (d, Convert
7: (datetime, '200701' + '01')) - 1) * -1), Convert
8: (datetime, '200701' + '01'))), dateadd
9: (d, -1, dateadd 10: (m, 1, dateadd 11: (d, ((datepart 12: (d, Convert
13: (datetime, '200701' + '01')) - 1) * -1), Convert
14: (datetime, '200701' + '01'))))) + 1)
15: map.map_id AS tgl
16: FROM msdb.dbo.MSdbms_map map) Kldr
The output is as shown below:
Tgl2
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
The RIGHT( '00' + CONVERT(VARCHAR(2), Id), 2) is functioning the same way with LPAD in Oracle.
alo bang bontor how are you?
ReplyDeleteusing sqlserver 2005:
Select noid,name from
(
select row_number() over(order by date) 'noid',name from table1
)
as a
using Sql server 2000 :
use pubs
GO
Select IDENTITY(int, 1,1) id, * INTO #Temp from authors
Select * from #Temp
Regards
Dear Fikril,
ReplyDeleteThanks for dropping by.
Well, another alternate solution. For I'm out of office, I'll try this later.
regards,
Bontor