Thursday, March 31, 2011
Viveka hits TDP member in Assembly
The ongoing disturbance in the Assembly touched a new low on Monday when Agriculture Minister Y.S. Vivekananda Reddy walked across the floor towards the Telugu Desam members to catch one of them, Ch. Prabhakar, by the collar and hit him.
Mr. Vivekananda Reddy was apparently incensed by the provocative slogans written on the placards held by the TDP members denouncing his brother and former Chief Minister Y.S. Rajasekhara Reddy. TDP members have been displaying such placards for the past two days alleging that YSR and his coterie had alienated vast chunks of government land to private parties for pecuniary benefit.
Losing his cool, the Minister snatched and tore three placards. Pointing his finger at the TDP members, he rebuked them in unprintable language for attacking his deceased brother. When he tried to move further menacingly, he was prevented by those in the first row like G. Muddukrishnama Naidu.
Mr. Ch. Prabhakar and K.E. Prabhakar, who were in the second row behind him, objected to his unruly behaviour after which there was a scuffle between the Minister and the TD members. This signalled bedlam on the floor. Congress members G. Srikanth Reddy and others owing loyalty to Y.S. Jaganmohan Reddy joined the Minister in hurling abuse at the TD members. The Minister was so overtaken by emotion that BJP floor leader G. Kishan Reddy, Ministers Syed Mohd Ahmadullah (Minorities Welfare), V. Vasanth Kumar (Tourism) and a dozen others, including MIM members, struggled for nearly 15 minutes to push him back to his seat.
Watching the high voltage drama helplessly, Deputy Speaker N. Manohar adjourned the House until after noon. When the House met in the evening, Mr. Vivekananda Reddy as well as Chief Minister N. Kiran Kumar Reddy tendered an apology to the House.
Agreement
There was hectic behind-the-scenes activity within the Congress as well as the Telugu Desam Party to make the Minister apologise. Apparently, the two sides reached an agreement under which the TDP dropped its demand for sacking the Minister and the government softened further on the demand to appoint a House Committee on irregular land allotments, the issue which triggered the whole ruckus.
Record-breaking session 2011 March
The budget session of the state assembly, which ends on Tuesday, will long be remembered for two unsavoury episodes that took place on the floor of the house. The session, in fact, began on a stormy note, when governor ESL Narasimhan faced the wrath of the Telangana members while he was addressing the joint session of the house. Yet another unsavoury episode took place on Monday when minister YS Vivekananda Reddy lost his cool and assaulted two TDP members on the floor of the house.
Andhra Pradesh - Loksabha
1 Adilabad
2 Peddapally
3 Karimnagar
4 Nizamabad
5 Zahirabad
6 Medak
7 Malkajgiri
8 Secunderabad
9 Hyderabad
10 Chevella
11 Mahbubnagar
12 Nagarkurnool
13 Nalgonda
14 Bhongir
15 Warangal
16 Mahbubabad
17 Khammam
18 Araku
19 Srikakulam
20 Vizianagaram
21 Visakhapatnam
22 Anakapalle
23 Kakinada
24 Amalapuram
25 Rajahmundry
26 Narsapuram
27 Eluru
28 Machilipatnam
29 Vijaywada
30 Guntur
31 Narasaraopet
32 Bapatla
33 Ongole
34 Nandyal
35 Kurnool
36 Anantapur
37 Hindupur
38 Kadapa
39 Nellore
40 Tirupati
41 Rajampet
42 Chittoor
2 Peddapally
3 Karimnagar
4 Nizamabad
5 Zahirabad
6 Medak
7 Malkajgiri
8 Secunderabad
9 Hyderabad
10 Chevella
11 Mahbubnagar
12 Nagarkurnool
13 Nalgonda
14 Bhongir
15 Warangal
16 Mahbubabad
17 Khammam
18 Araku
19 Srikakulam
20 Vizianagaram
21 Visakhapatnam
22 Anakapalle
23 Kakinada
24 Amalapuram
25 Rajahmundry
26 Narsapuram
27 Eluru
28 Machilipatnam
29 Vijaywada
30 Guntur
31 Narasaraopet
32 Bapatla
33 Ongole
34 Nandyal
35 Kurnool
36 Anantapur
37 Hindupur
38 Kadapa
39 Nellore
40 Tirupati
41 Rajampet
42 Chittoor
13th Assembly Elections in Andhra Pradesh 2009 Results
The Southern State of Andhra Pradesh went for Assembly Elections simultaneously with the Lok Sabha Elections. The ruling Congress party under Chief Minister Dr.Y.S.Rajasekhara Reddy not only survived against the Anti-incumbency factor but also managed to pip a strong Grand Alliance led by the Telugu Desam Party along with the TRS and the Communists. Megastar Chiranjeevi’s Praja Rajyam Party turned out to be a dud in its maiden elections but did manage to split the Anti-establishment votes.
The Congress for the second time was in a position to form the Government on its own, this time its tally did fall down to 158 from the 2004 figure of 185. YSR could well be ruling the state for a total of 10 years which could beat Chandrababu’s rule of 9 years prior to that. The TDP came second with 95 seats, clearly doubling its numbers from 2004 but just not enough numbers to come back in power.
The Telangana Rashtra Samithi (TRS) was reduced to a meagre 10 seats this time and in a way perhaps putting doubts about the formation of the Telangana State.
The Congress got past the magic figure of 148 but had to face embarassment of quite a few Ministers and also Speaker KR Suresh Reddy losing the elections! The State Congress No.2 D.Srinivas also was embarassed in his Nizamabad (Urban) constituency. The Elections saw the maiden entry of Chiranjeevi into the Assembly, the PRP founder won from Tirupati but had to face defeat in Palacole!
Here is the breakdown of the seats won out of the 294 Assembly Seats by the various parties :
The Congress for the second time was in a position to form the Government on its own, this time its tally did fall down to 158 from the 2004 figure of 185. YSR could well be ruling the state for a total of 10 years which could beat Chandrababu’s rule of 9 years prior to that. The TDP came second with 95 seats, clearly doubling its numbers from 2004 but just not enough numbers to come back in power.
The Telangana Rashtra Samithi (TRS) was reduced to a meagre 10 seats this time and in a way perhaps putting doubts about the formation of the Telangana State.
The Congress got past the magic figure of 148 but had to face embarassment of quite a few Ministers and also Speaker KR Suresh Reddy losing the elections! The State Congress No.2 D.Srinivas also was embarassed in his Nizamabad (Urban) constituency. The Elections saw the maiden entry of Chiranjeevi into the Assembly, the PRP founder won from Tirupati but had to face defeat in Palacole!
Here is the breakdown of the seats won out of the 294 Assembly Seats by the various parties :
- Indian National Congress (INC) – 157
- Telugu Desam Party (TDP) – 91
- Praja Rajyam Party (PRP) – 18
- Telangana Rashtra Samithi (TRS) – 10
- Majlis-e-Ittehadul Muslimeen (MIM) – 07
- Communist Party of India (CPI) – 04
- Independents – 03
- Bharatiya Janata Party (BJP) – 02
- Communist Party of India (Marxist) (CPI-M) – 01
- Lok Satta – 01
Why did Cabinet Ministers Resign? – Significant Minister Portfolios
This article deals with the reasons for Andhra Pradesh Cabinet Ministers resignation. It tries to define what might be the significant minister portfolios and a partial distribution of the significant minister posts.
Initially all the ministers were unsatisfied with the portfolio given to them personally. They stated openly that they were given insignificant posts and they deserve better minister posts. After the involvement of the Congress High Command everyone of them calmed. They change their statement that the cabinet minister posts distribution was partial towards Reddy community and many other communities were purposely let down. This partial distribution is the reason for the did-satisfaction of many ministers and not their personal post.
Significant minister portfolios are mostly those in which has a good budget allocation. There are 11 portfolios which are distinctly considered as significant. They are:
• Finance
• Revenue
• Home
• Health
• Major Irrigation
• Panchayat Raj
• Municipal Administration and Urban Development
• Electricity
• Major Industries
• Roads and Buildings
Number of Reddy's in Significant Portfolios – 3
Number of BC's in Significant Portfolios – 5
Number of OC's(other than reddy) in Significant Portfolios – 2
Number of SC's in Significant Portfolios – 2
Number of Minorities in Significant Portfolios – 1
Number of Reddy's in Significant Portfolios – 4
Number of BC's in Significant Portfolios – 6
Number of OC's(other than reddy) in Significant Portfolios – 6
Number of SC's in Significant Portfolios – 0
Number of Minorities in Significant Portfolios – 0
Number of Reddy's in Significant Portfolios – 8
Number of BC's in Significant Portfolios – 2
Number of OC's(other than reddy) in Significant Portfolios – 0
Number of SC's in Significant Portfolios – 1
Number of Minorities in Significant Portfolios – 0
One can clearly see an increase of the Reddy's in the significant posts. This partiality is the reason for many Andhra Pradesh Cabinet Ministers to show dissatisfaction on the distribution.
Initially all the ministers were unsatisfied with the portfolio given to them personally. They stated openly that they were given insignificant posts and they deserve better minister posts. After the involvement of the Congress High Command everyone of them calmed. They change their statement that the cabinet minister posts distribution was partial towards Reddy community and many other communities were purposely let down. This partial distribution is the reason for the did-satisfaction of many ministers and not their personal post.
What are significant minister portfolios
Significant minister portfolios are mostly those in which has a good budget allocation. There are 11 portfolios which are distinctly considered as significant. They are:
• Finance
• Revenue
• Home
• Health
• Major Irrigation
• Panchayat Raj
• Municipal Administration and Urban Development
• Electricity
• Major Industries
• Roads and Buildings
Distribution of Significant Portfolios in 2004-2009 Cabinet
Number of Reddy's in Significant Portfolios – 3
Number of BC's in Significant Portfolios – 5
Number of OC's(other than reddy) in Significant Portfolios – 2
Number of SC's in Significant Portfolios – 2
Number of Minorities in Significant Portfolios – 1
Distribution of Significant Portfolios in 2009-2010 Cabinet
Number of Reddy's in Significant Portfolios – 4
Number of BC's in Significant Portfolios – 6
Number of OC's(other than reddy) in Significant Portfolios – 6
Number of SC's in Significant Portfolios – 0
Number of Minorities in Significant Portfolios – 0
Distribution of Significant Portfolios in Kiran Kumar Reddy Cabinet
Number of Reddy's in Significant Portfolios – 8
Number of BC's in Significant Portfolios – 2
Number of OC's(other than reddy) in Significant Portfolios – 0
Number of SC's in Significant Portfolios – 1
Number of Minorities in Significant Portfolios – 0
One can clearly see an increase of the Reddy's in the significant posts. This partiality is the reason for many Andhra Pradesh Cabinet Ministers to show dissatisfaction on the distribution.
Kiran Kumar Reddy Cabinet Distribution Statistics
Krishna Kumar Reddy distributed the Cabinet minister posts quite evenly among all the casts, regions etc. This article gives a detailed distribution of the Andhra Pradesh Cabinet Ministers with respect to regions and caste. Interesting Statistics regarding the Kiran Kumar Reddy Cabinet are given here.
Number of Cabinet ministers that were not present in YSR Cabinet or Rosaiah Cabinet : 11
They are Thota Narasimham(East Godavari), Kasu Krishna Reddy(Guntur), M. Maheedhar Reddy(Prakasam), D.L. Ravindra Reddy(Kadapa), Y.S. Vivekananda Reddy(Kadapa), S. Sailajanath(Anatapur), Pratap Reddy (Kurnool), T.G Venkatesh(Kurnool), P. Shanker Rao(Hyderabad), Basavaraju Saraiah(Warangal), Jana Reddy(Nalgonda)
Number of first-time Cabinet Ministers: 7
They are Thota Narasimham(East Godavari), M. Maheedhar Reddy(Prakasam), Y.S. Vivekananda Reddy(Kadapa), S. Sailajanath(Anatapur), Pratap Reddy (Kurnool), T.G Venkatesh(Kurnool), Basavaraju Saraiah(Warangal)
Number of Rosaiah Cabinet ministers who lost their minister posts:5
They are Pilli Subhash Chandra, Balineni Srinivas Reddy, P. Ramachandra Reddy, Chinta Mohan Reddy, Gade Venkat Reddy
Number of Cabinet Ministers from Telangana Region : 16
Number of Cabinet ministers from Coastal Andhra Region : 15
Number of Cabinet ministers from Rayalaseema Region : 8(including CM)
Number of Cabinet Ministers of SC Community : 6
Number of Cabinet Ministers of ST Community : 1
Number of Cabinet Ministers of Muslim/Minority Community : 1
Number of Cabinet Ministers of Backward Class (BC) Section : 10
Number of Cabinet Ministers of Reddy Community : 14 (including the CM)
Number of Cabinet Ministers of Kapu Community : 3
Number of Cabinet Ministers of Velama Community : 1
Number of Cabinet Ministers of Vysya Community : 1
Number of Cabinet Ministers of Kshatriya Community : 1
Number of Cabinet Ministers of Brahmin Community : 1
Number of Cabinet Ministers of Kamma Community : 1
Thus a total of 18 minister posts were given to the backward classes (SC,ST,BC,Minorities), which accounts to almost 45% of the Cabinet.
Number of Cabinet ministers that were not present in YSR Cabinet or Rosaiah Cabinet : 11
They are Thota Narasimham(East Godavari), Kasu Krishna Reddy(Guntur), M. Maheedhar Reddy(Prakasam), D.L. Ravindra Reddy(Kadapa), Y.S. Vivekananda Reddy(Kadapa), S. Sailajanath(Anatapur), Pratap Reddy (Kurnool), T.G Venkatesh(Kurnool), P. Shanker Rao(Hyderabad), Basavaraju Saraiah(Warangal), Jana Reddy(Nalgonda)
Number of first-time Cabinet Ministers: 7
They are Thota Narasimham(East Godavari), M. Maheedhar Reddy(Prakasam), Y.S. Vivekananda Reddy(Kadapa), S. Sailajanath(Anatapur), Pratap Reddy (Kurnool), T.G Venkatesh(Kurnool), Basavaraju Saraiah(Warangal)
Number of Rosaiah Cabinet ministers who lost their minister posts:5
They are Pilli Subhash Chandra, Balineni Srinivas Reddy, P. Ramachandra Reddy, Chinta Mohan Reddy, Gade Venkat Reddy
Regional wise Distribution of Andhra Pradesh Cabinet
Number of Cabinet Ministers from Telangana Region : 16
Number of Cabinet ministers from Coastal Andhra Region : 15
Number of Cabinet ministers from Rayalaseema Region : 8(including CM)
Caste wise Distribution of Andhra Pradesh Cabinet
Number of Cabinet Ministers of SC Community : 6
Number of Cabinet Ministers of ST Community : 1
Number of Cabinet Ministers of Muslim/Minority Community : 1
Number of Cabinet Ministers of Backward Class (BC) Section : 10
Number of Cabinet Ministers of Reddy Community : 14 (including the CM)
Number of Cabinet Ministers of Kapu Community : 3
Number of Cabinet Ministers of Velama Community : 1
Number of Cabinet Ministers of Vysya Community : 1
Number of Cabinet Ministers of Kshatriya Community : 1
Number of Cabinet Ministers of Brahmin Community : 1
Number of Cabinet Ministers of Kamma Community : 1
Thus a total of 18 minister posts were given to the backward classes (SC,ST,BC,Minorities), which accounts to almost 45% of the Cabinet.
Designations of Kiran Kumar Reddy Cabinet Ministers (2010-2014)
N. Kiran Kumar Reddy – Chief minister of Andhra Pradesh, General administration minister of Andhra Pradesh, Commercial Taxes minister of Andhra Pradesh, Law & Order minister of Andhra Pradesh, Energy, Coal and Boilers minister of Andhra Pradesh
S. Sailajanath - Primary Education &SSC Minister of Andhra Pradesh
K. Parthasarathi - Secondary Education & Govt. Exams Minister of Andhra Pradesh
D. Raj Narasimha - Higher & Technical education Minister of Andhra Pradesh
S. Vijayarama Raju - Forest, Environment, Science & Tech Minister of Andhra Pradesh
P. Viswarup - Animal Husbandry, Diary development, Fisheries & Veterinary University Minister of Andhra Pradesh
R. Venkata Reddy - Horticulture & Sericulture Minister of Andhra Pradesh
A. Sudarshan Reddy - Major & Medium Irrigation Minister of Andhra Pradesh
Y.S. Vivekananda Reddy – Agriculture Minister of Andhra Pradesh
T.G. Venkatesh - Minor Irrigation Minister of Andhra Pradesh
P. Balaraju - Tribal Welfare Minister of Andhra Pradesh
P. Satyanarayana - Social Welfare Minister of Andhra Pradesh
B. Saraiah - BC Welfare Minister of Andhra Pradesh
Ahmadullah - Minority Welfare & Wakf Minister of Andhra Pradesh
V. Sunitha Laxma Reddy - IKP, Pensions & Women affairs Minister of Andhra Pradesh
D.L. Ravindra Reddy – Health Minister of Andhra Pradesh, Arogyasri & Medical Education Minister of Andhra Pradesh
K. Jana Reddy - Panchayat Raj Minister
D. Prasada Rao - Roads & Buildings Minister of Andhra Pradesh
B. Satyanarayana – Transport Minister of Andhra Pradesh
K. Venkata Reddy - Infra, Ports, Airports & Natural Gas Minister of Andhra Pradesh
G. Aruna Kumari - Mines & Geology Minister of Andhra Pradesh
P. Sankara Rao - Handlooms & Textiles Minister of Andhra Pradesh
M. Mukesh Goud - Marketing & Warehousing Minister of Andhra Pradesh
J. Geeta Reddy - Major Industries Minister of Andhra Pradesh & Sugar Minister of Andhra Pradesh
D. Nagender - Labour Minister of Andhra Pradesh
P. Lakshmaiah - Information Technology Minister of Andhra Pradesh
M. Maheedhar Reddy - Municipal administration & urban development Minister of Andhra Pradesh
K. Lakshminarayana – Housing Minister of Andhra Pradesh
D. Manikya Varaprasad - Rural development & NREGP Minister of Andhra Pradesh
D.K. Aruna - Information & Public Relations Minister of Andhra Pradesh
P. Sabita Indra Reddy – Home Minister of Andhra Pradesh
E. Pratap Reddy - Law and Courts Minister of Andhra Pradesh
D. Sridhar Babu - Civil Supplies Affairs Minister of Andhra Pradesh & Legislative Affairs Minister of Andhra Pradesh
A.Ramanarayana Reddy – Finance Minister of Andhra Pradesh
M. Venkata Ramana Rao – Excise Minister of Andhra Pradesh
N. Raghuveera Reddy - Revenue Minister of Andhra Pradesh, Relief & Rehabilitation Minister of Andhra Pradesh
J. Krishna Rao – Endowments Minister of Andhra Pradesh
T. Narasimham - Stamp and Registration Minister of Andhra Pradesh
V. Vasanth Kumar – Tourism Minister of Andhra Pradesh &Youth Affairs Minister of Andhra Pradesh
K. Krishna Reddy - Co-operation Minister of Andhra Pradesh
Educational Ministers of Andhra Pradesh(2010-2014)
S. Sailajanath - Primary Education &SSC Minister of Andhra Pradesh
K. Parthasarathi - Secondary Education & Govt. Exams Minister of Andhra Pradesh
D. Raj Narasimha - Higher & Technical education Minister of Andhra Pradesh
Agriculture and Animals related Ministers in Andhra Pradesh (2010-2014)
S. Vijayarama Raju - Forest, Environment, Science & Tech Minister of Andhra Pradesh
P. Viswarup - Animal Husbandry, Diary development, Fisheries & Veterinary University Minister of Andhra Pradesh
R. Venkata Reddy - Horticulture & Sericulture Minister of Andhra Pradesh
A. Sudarshan Reddy - Major & Medium Irrigation Minister of Andhra Pradesh
Y.S. Vivekananda Reddy – Agriculture Minister of Andhra Pradesh
T.G. Venkatesh - Minor Irrigation Minister of Andhra Pradesh
Social Welfare Related Ministers of Andhra Pradesh (2010-2014)
P. Balaraju - Tribal Welfare Minister of Andhra Pradesh
P. Satyanarayana - Social Welfare Minister of Andhra Pradesh
B. Saraiah - BC Welfare Minister of Andhra Pradesh
Ahmadullah - Minority Welfare & Wakf Minister of Andhra Pradesh
V. Sunitha Laxma Reddy - IKP, Pensions & Women affairs Minister of Andhra Pradesh
D.L. Ravindra Reddy – Health Minister of Andhra Pradesh, Arogyasri & Medical Education Minister of Andhra Pradesh
K. Jana Reddy - Panchayat Raj Minister
Resources and Infrastructure Related Ministers in Andhra Pradesh (2010-2014)
D. Prasada Rao - Roads & Buildings Minister of Andhra Pradesh
B. Satyanarayana – Transport Minister of Andhra Pradesh
K. Venkata Reddy - Infra, Ports, Airports & Natural Gas Minister of Andhra Pradesh
G. Aruna Kumari - Mines & Geology Minister of Andhra Pradesh
Companies and Industries Related Ministers in Andhra Pradesh (2010-2014)
P. Sankara Rao - Handlooms & Textiles Minister of Andhra Pradesh
M. Mukesh Goud - Marketing & Warehousing Minister of Andhra Pradesh
J. Geeta Reddy - Major Industries Minister of Andhra Pradesh & Sugar Minister of Andhra Pradesh
D. Nagender - Labour Minister of Andhra Pradesh
P. Lakshmaiah - Information Technology Minister of Andhra Pradesh
Administration and Development Related Ministers of Andhra Pradesh (2010-2014)
M. Maheedhar Reddy - Municipal administration & urban development Minister of Andhra Pradesh
K. Lakshminarayana – Housing Minister of Andhra Pradesh
D. Manikya Varaprasad - Rural development & NREGP Minister of Andhra Pradesh
D.K. Aruna - Information & Public Relations Minister of Andhra Pradesh
Law and Order related Ministers of Andhra Pradesh (2010-2014)
P. Sabita Indra Reddy – Home Minister of Andhra Pradesh
E. Pratap Reddy - Law and Courts Minister of Andhra Pradesh
D. Sridhar Babu - Civil Supplies Affairs Minister of Andhra Pradesh & Legislative Affairs Minister of Andhra Pradesh
Finance relater Ministers of Andhra Pradesh (2010-2014)
A.Ramanarayana Reddy – Finance Minister of Andhra Pradesh
M. Venkata Ramana Rao – Excise Minister of Andhra Pradesh
N. Raghuveera Reddy - Revenue Minister of Andhra Pradesh, Relief & Rehabilitation Minister of Andhra Pradesh
J. Krishna Rao – Endowments Minister of Andhra Pradesh
Tourism,Co-Operation and Stam and Registration Ministers of Andhra Pradesh (2010-2014)
T. Narasimham - Stamp and Registration Minister of Andhra Pradesh
V. Vasanth Kumar – Tourism Minister of Andhra Pradesh &Youth Affairs Minister of Andhra Pradesh
K. Krishna Reddy - Co-operation Minister of Andhra Pradesh
Andhra Pradesh MLC elections/election Results & Winners 2011
Andhra Pradesh MLC elections/election Results & Winners 2011
ANDHRA PRADESH MLC ELECTIONS RESULTS 2011 AND WINNERS ANNOUNCED TODAY 23RD MARCH 2011............
Andhra Pradesh AP MLC Elections 2011 counting began today on 23rd March 2011 at 8:00 AM. If You can download online MLC Elections Andhra Pradesh 2011, MLC Election Results 2011 by evening as the Election Commission will announce MLC Results 2011. In MLC Elections 2011 after polling for the election of nine MLCs from local authority constituencies concluded on Monday, the Congress appeared to be in a much better position than anticipated.
WINNERS OF THE MLC ELECTIONS 2011
Jagan Group:
- Narayana Reddy-Kadapa
- Desai Tippa Reddy-Chittur
- Meka Seshu Babu-West Godavari
Congress Party:
- Vakati Narayana Reddy-PSR.Nellore
- SV.Mohan Reddy-Karnool
- Viswa Prasad-Srikakulam
TDP
- Boddu Bhaskar Rao-East Godavari
- Mettu Govindha Reddy-Anatapuram
- Angara Ram Mohan Rao-West Godavari
Anantapur: Telugu Desam Partycandidate Mettu Govinda Reddy defeated Congress candidate Patil Venugopal Reddy by 53 votes.
East Godavari: Telugu Desam Partycandidate Boddu Bhaskara Raarao won by defeated his rival by 174 votes.
West Godavari: Telugu Desam Party candidate Angara Rammohan Rao capture second seat.
Nellore:Telugu Desam Party candidate Beeda Ravichandra Yadav lost due to cross- voting by Telugu Desam Party voters.
AP Assembly Consituencies
ADHILABAD (10 Assembly Consituencies)
1. SIRPUR
2. CHENNUR
3. BELLAMPALL
4. MANCHERIAL
5. ASIFABAD
6. KHANPUR
7. ADHILABAD
8. BOATHAN
9. NIRMAL
10. MUDHOLE
HYDERABAD (15 Assembly Consituencies)
11.MUSHEERABAD
12. MALAKPET
13. AMBERPET
14. KHIARTHABAD
15. JUBLEEHILLS
16. SANATHNAGAR
17. NAMPALLY
18. KHRAWAN
19. GOSHMAHAL
20. CHARMINAR
21. CHANDRYANGUTTA
22. YAKUTHPURA
23. BAHADURPURA
24. SECENDRABAD
25. CANTONMENT
KARIMNAGAR (13 Assembly Consituencies)
26. KORATLA
27. JAGITHIAL
28. DHARMAPURI
29. RAMAGUNDAM
30. MANTAIN
31. PEDDAPALLE
32. KARIMNAGAR
33. CHOPPADANDI
34. VEMULAWADA
35. SIRICILLA
36. MANAKONDUR
37. HUZURABAD
38. HUSNABAD
KHAMMAM (10 Assembly Consituencies)
39. PINAPAKA
40. YELLANDU
41. KHAMMAM
42. PALAIR
43. MADHIRA
44. WYRA
45. SATHUPALLE
46. KOTHAGUDAM
47. ASWARAOPETA
48. BHADRACHELAM
MAHABUBNAGAR (14 Assembly Consituencies)
49. KONDAGAL
50. NARAYANPET
51. MAHABUBNAGAR
52. JADCHERLA
53. DEVERKADRA
54. MAKTHAL
55. WANAPARTHY
56. GADWAL
57. ALAMPUR
58. NAGARKURNOOL
59. ACHAMPETA
60. KALWAKURTHY
61. SHADNAGAR
62. KOLLAPUR
MEDAK (10 Assembly Consituencies)
63. SIDDIPET
64. MEDAK
65. NARAYANKHED
66. ANDOLE
67. NARSAPUR
68. ZAHIRABAD
69. SANGAREDDY
70. PATANCHERU
71. DUBBAK
72.GAJWEL
NALGONDA (12 Assembly Consituencies)
73. DEVARAKONDA
74. NAGARJUN SAGAR
75. MIRYALAGUDA
76. HUZURNAGAR
77. KODA
78. SURYAPET
79. NALGONDA
80. MUNUGODU
81. BHONGIRI
82. NAKREKAL
83. ALAIR
84. THUNGATHURTHY
NIZAMBAD (9 Assembly Consituencies)
85. ARMUR
86. BODHAN
87. JUKKAL
88. BANSWADA
89. BALAKONDA
90. YELLAREDDY
91. KAMAREDDY
92. NIZAMBAD [URBAN]
93. NIZAMBAD [RURAL]
RANGAREDDY (14 Assembly Consituencies)
94. MEDCHEL
95. MALKAJGIRI
96. QUTHBULLAPUR
97. UPPAL
98. KUKATPALLY
99. IBRAHIMPATNAM
100. L B NAGAR
101. MAHESWARAM
102. RAJENDRA NAGAR
103. SERLINGAMPALLY
104. CHEVELLA
105. PARIGI
106. VIKARABAD
107. TANDURU
WARANGAL (12 Assembly Consituencies)
108. JANGAM
109. STATION GHAPUR
110. PALAKURTHI
111. DORNAKAL
112. MAHABUBABAD
113. NARSAMPETA
114. PARKALA
115. WARANGAL [EAST]
116. WARANGAL [WEST]
117. WARDHANNAPET
118. BHUPALAPALLE
119. MULUGU
EAST GIDAVARI (19 Assembly Consituencies)
120. TUNI
121. PRATHIPADU
122. PITAPURAM
123. KAKINADA [RURAL]
124. PEDDAPURAM
125. ANAPARTHY
126. KAKINADA [CITY]
127. RAMACHANDRAPURAM
128. MUMMIDIVARAM
129. AMALAPURAM
130. RAZOL
131. GANNAVARAM
132. KOTHAKOTA
133. MANDAPETA
134. BURUGUPUDA
135. RAJAMANDRY [CITY]
136. RAJAMANDRY [RURAL]
137. JAGGAMPETA
138. RAMPACHODAVARAM
GUNTUR (17 Assembly Consituencies)
139. PEDAKURAPADU
140. TADIKONDA
141. MANGALAGIRI
142. PONNUR
143. VEMURU
144. REPALLE
145. TENALI
146. BAPATLA
147. PRATHIPADU
148. GUNTUR [EAST]
149. GUNTUR [WEST]
150. GURAJALA
151. CHILAKALURIPETA
152. NARSARAOPETA
153. VINUKONDA
154. SATHENAPALLE
155. MACHERLA
KRISHNA (16 Assembly Consituencies)
156. TIRIVUR
157. NUZIVIDU
158. GANNAVARAM
159. GUDIWADA
160. KIKALURU
161. PEDANA
162. MACHLIPATNAM
163. AVANIGADDA
164. PAMARU
165. PENAMALURU
166. VIJAYAWADA [EAST]
167. VIJAYAWADA [WEST]
168. VIJAYAEADA [CENTRAL]
169. MYLAVARAM
170. NANDIGAMA
171. JAGGAYAPETA
NELLORE (10 Assembly Consituencies)
172. KAVALI
173. ATMAKUR
174. KOVUR
175. NELLORE [CITY]
176. NELLORE [RURAL]
177. SARVEYPALLY
178. GUDURU
179. SULLURUPETA
180. VENKATAGIRI
181. UDAYAGIRI
PRAKASHAM (12 Assembly Consituencies)
182. YERRRAGONDAPALEM
183. DARSHI
184. PARCHEVUR
185 CHIRALA
186. ADDANKI
187. SANTHANUTHALAPADU
188. ONGOLE
189. KANDAKUR
190. KONDAPI
191. MARKAPURAM
192. GIDDALUR
193. KANIGIRI
SRIKAKULAM (10 Assembly Consituencies)
194. ITCHAPURAM
195. PALASA
196. TELKKALI
197. PATHAPATNAM
198. SRIKAKULAM
199. AMUDHALAVALASA
200. ETCHERLA
201. NARSANNAPETA
202. RAJAM
203. PALAKONDA
VIZAG (15 Assembly Consituencies)
204. BHIMILI
205. VIZAG [EAST]
206. VIZAG [WEST]
207. VIZAG [NORTH]
208. VIZAG [SOUTH]
209. GAJUWAKA
210. CHODAVARAM
211. MADUGULA
212. ARAKU
213. PADERU
214. ANAKAPALLE
215. PENDURTHI
216. YELAMANCHILI
217. PAYAKARAOPETA
218. NARSTPATNAM
VIJAYANAGARAM (9 Assembly Consituencies)
219. KURUPAM
220. PRAVATHIPURAM
221. SALURU
222. BOBBILI
223CHEEPURAPALLE
224. GANAPATHI NAGARAM
225. NELLIMARLA
226. VIJAYANAGARAM
227. SRUNGAVARAPUKOTA
WEST GODAVARI (15 Assembly Consituencies)
228. KOVUR
229. NIDADAVOLE
230. ACHANTA
231. PALACOLE
232. NARSAPURAM
233. BHIMAVARAM
234. UNDI
235 TANUKU
235. TADEPALLIGUDEM
236. UNGUTUR
237. DENDULURU
238. ELURU
239. GOPALAPURAM
240. POLAVARAM
242. CHINTALAPUDI
ANANTAPUR (14 Assembly Consituencies)
243. RAYADURGA
244. URAVAKONDA
245. GUNTAKAL
246. TADIPATRI
247. SINGANAMALA
248. ANANTAPUR
249. KALYAN DURGA
250. RAPTADU
251. MADAKASI
252. HINDUPUR
253. PENUKONDA
254. PUTTAPATRI
255. DHARMAVARAM
256. KADIRI
CHITOOR (14 Assembly Consituencies)
257. THAMBALAPALLE
258. PILERU
259. MADANAPALLE
260. PUNGANUR
261. CHANDRAGIRI
262. THIRUPATHI
263. SRIKALAHASTI
264. SATYAVEDU
265. NAGIRI
266. GANGADHRA
267. CHITOOR
268. KUPPAM
269. PUTHALAPATU
270. PALAMANER
KADAPA (10 Assembly Consituencies)
271. BADWEL
272. RAJAMPETA
273. KADAPA
274. KODURU
275. RAYACHOT
276. PULIVENDULLA
277. KAMALAPURAM
278. JAMMALAMADUGU
279. PRODDUTUR
280. MYDKUR
KURNOOL (14 Assembly Consituencies)
1. SIRPUR
2. CHENNUR
3. BELLAMPALL
4. MANCHERIAL
5. ASIFABAD
6. KHANPUR
7. ADHILABAD
8. BOATHAN
9. NIRMAL
10. MUDHOLE
HYDERABAD (15 Assembly Consituencies)
11.MUSHEERABAD
12. MALAKPET
13. AMBERPET
14. KHIARTHABAD
15. JUBLEEHILLS
16. SANATHNAGAR
17. NAMPALLY
18. KHRAWAN
19. GOSHMAHAL
20. CHARMINAR
21. CHANDRYANGUTTA
22. YAKUTHPURA
23. BAHADURPURA
24. SECENDRABAD
25. CANTONMENT
KARIMNAGAR (13 Assembly Consituencies)
26. KORATLA
27. JAGITHIAL
28. DHARMAPURI
29. RAMAGUNDAM
30. MANTAIN
31. PEDDAPALLE
32. KARIMNAGAR
33. CHOPPADANDI
34. VEMULAWADA
35. SIRICILLA
36. MANAKONDUR
37. HUZURABAD
38. HUSNABAD
KHAMMAM (10 Assembly Consituencies)
39. PINAPAKA
40. YELLANDU
41. KHAMMAM
42. PALAIR
43. MADHIRA
44. WYRA
45. SATHUPALLE
46. KOTHAGUDAM
47. ASWARAOPETA
48. BHADRACHELAM
MAHABUBNAGAR (14 Assembly Consituencies)
49. KONDAGAL
50. NARAYANPET
51. MAHABUBNAGAR
52. JADCHERLA
53. DEVERKADRA
54. MAKTHAL
55. WANAPARTHY
56. GADWAL
57. ALAMPUR
58. NAGARKURNOOL
59. ACHAMPETA
60. KALWAKURTHY
61. SHADNAGAR
62. KOLLAPUR
MEDAK (10 Assembly Consituencies)
63. SIDDIPET
64. MEDAK
65. NARAYANKHED
66. ANDOLE
67. NARSAPUR
68. ZAHIRABAD
69. SANGAREDDY
70. PATANCHERU
71. DUBBAK
72.GAJWEL
NALGONDA (12 Assembly Consituencies)
73. DEVARAKONDA
74. NAGARJUN SAGAR
75. MIRYALAGUDA
76. HUZURNAGAR
77. KODA
78. SURYAPET
79. NALGONDA
80. MUNUGODU
81. BHONGIRI
82. NAKREKAL
83. ALAIR
84. THUNGATHURTHY
NIZAMBAD (9 Assembly Consituencies)
85. ARMUR
86. BODHAN
87. JUKKAL
88. BANSWADA
89. BALAKONDA
90. YELLAREDDY
91. KAMAREDDY
92. NIZAMBAD [URBAN]
93. NIZAMBAD [RURAL]
RANGAREDDY (14 Assembly Consituencies)
94. MEDCHEL
95. MALKAJGIRI
96. QUTHBULLAPUR
97. UPPAL
98. KUKATPALLY
99. IBRAHIMPATNAM
100. L B NAGAR
101. MAHESWARAM
102. RAJENDRA NAGAR
103. SERLINGAMPALLY
104. CHEVELLA
105. PARIGI
106. VIKARABAD
107. TANDURU
WARANGAL (12 Assembly Consituencies)
108. JANGAM
109. STATION GHAPUR
110. PALAKURTHI
111. DORNAKAL
112. MAHABUBABAD
113. NARSAMPETA
114. PARKALA
115. WARANGAL [EAST]
116. WARANGAL [WEST]
117. WARDHANNAPET
118. BHUPALAPALLE
119. MULUGU
EAST GIDAVARI (19 Assembly Consituencies)
120. TUNI
121. PRATHIPADU
122. PITAPURAM
123. KAKINADA [RURAL]
124. PEDDAPURAM
125. ANAPARTHY
126. KAKINADA [CITY]
127. RAMACHANDRAPURAM
128. MUMMIDIVARAM
129. AMALAPURAM
130. RAZOL
131. GANNAVARAM
132. KOTHAKOTA
133. MANDAPETA
134. BURUGUPUDA
135. RAJAMANDRY [CITY]
136. RAJAMANDRY [RURAL]
137. JAGGAMPETA
138. RAMPACHODAVARAM
GUNTUR (17 Assembly Consituencies)
139. PEDAKURAPADU
140. TADIKONDA
141. MANGALAGIRI
142. PONNUR
143. VEMURU
144. REPALLE
145. TENALI
146. BAPATLA
147. PRATHIPADU
148. GUNTUR [EAST]
149. GUNTUR [WEST]
150. GURAJALA
151. CHILAKALURIPETA
152. NARSARAOPETA
153. VINUKONDA
154. SATHENAPALLE
155. MACHERLA
KRISHNA (16 Assembly Consituencies)
156. TIRIVUR
157. NUZIVIDU
158. GANNAVARAM
159. GUDIWADA
160. KIKALURU
161. PEDANA
162. MACHLIPATNAM
163. AVANIGADDA
164. PAMARU
165. PENAMALURU
166. VIJAYAWADA [EAST]
167. VIJAYAWADA [WEST]
168. VIJAYAEADA [CENTRAL]
169. MYLAVARAM
170. NANDIGAMA
171. JAGGAYAPETA
NELLORE (10 Assembly Consituencies)
172. KAVALI
173. ATMAKUR
174. KOVUR
175. NELLORE [CITY]
176. NELLORE [RURAL]
177. SARVEYPALLY
178. GUDURU
179. SULLURUPETA
180. VENKATAGIRI
181. UDAYAGIRI
PRAKASHAM (12 Assembly Consituencies)
182. YERRRAGONDAPALEM
183. DARSHI
184. PARCHEVUR
185 CHIRALA
186. ADDANKI
187. SANTHANUTHALAPADU
188. ONGOLE
189. KANDAKUR
190. KONDAPI
191. MARKAPURAM
192. GIDDALUR
193. KANIGIRI
SRIKAKULAM (10 Assembly Consituencies)
194. ITCHAPURAM
195. PALASA
196. TELKKALI
197. PATHAPATNAM
198. SRIKAKULAM
199. AMUDHALAVALASA
200. ETCHERLA
201. NARSANNAPETA
202. RAJAM
203. PALAKONDA
VIZAG (15 Assembly Consituencies)
204. BHIMILI
205. VIZAG [EAST]
206. VIZAG [WEST]
207. VIZAG [NORTH]
208. VIZAG [SOUTH]
209. GAJUWAKA
210. CHODAVARAM
211. MADUGULA
212. ARAKU
213. PADERU
214. ANAKAPALLE
215. PENDURTHI
216. YELAMANCHILI
217. PAYAKARAOPETA
218. NARSTPATNAM
VIJAYANAGARAM (9 Assembly Consituencies)
219. KURUPAM
220. PRAVATHIPURAM
221. SALURU
222. BOBBILI
223CHEEPURAPALLE
224. GANAPATHI NAGARAM
225. NELLIMARLA
226. VIJAYANAGARAM
227. SRUNGAVARAPUKOTA
WEST GODAVARI (15 Assembly Consituencies)
228. KOVUR
229. NIDADAVOLE
230. ACHANTA
231. PALACOLE
232. NARSAPURAM
233. BHIMAVARAM
234. UNDI
235 TANUKU
235. TADEPALLIGUDEM
236. UNGUTUR
237. DENDULURU
238. ELURU
239. GOPALAPURAM
240. POLAVARAM
242. CHINTALAPUDI
ANANTAPUR (14 Assembly Consituencies)
243. RAYADURGA
244. URAVAKONDA
245. GUNTAKAL
246. TADIPATRI
247. SINGANAMALA
248. ANANTAPUR
249. KALYAN DURGA
250. RAPTADU
251. MADAKASI
252. HINDUPUR
253. PENUKONDA
254. PUTTAPATRI
255. DHARMAVARAM
256. KADIRI
CHITOOR (14 Assembly Consituencies)
257. THAMBALAPALLE
258. PILERU
259. MADANAPALLE
260. PUNGANUR
261. CHANDRAGIRI
262. THIRUPATHI
263. SRIKALAHASTI
264. SATYAVEDU
265. NAGIRI
266. GANGADHRA
267. CHITOOR
268. KUPPAM
269. PUTHALAPATU
270. PALAMANER
KADAPA (10 Assembly Consituencies)
271. BADWEL
272. RAJAMPETA
273. KADAPA
274. KODURU
275. RAYACHOT
276. PULIVENDULLA
277. KAMALAPURAM
278. JAMMALAMADUGU
279. PRODDUTUR
280. MYDKUR
KURNOOL (14 Assembly Consituencies)
281. ALLAGADDA
282. SRISHAILAM
283. NANDIKOTKUR
284. KURNOOL
285. PANYAM
286. NANDYALA
287. DHONE
288. PATTIKONDA
289. KODMUR
290. ADHONE
291. YAMMIGANUR
292. MANTRALAYAM
293.BANGANAPALLE
294 ALURU
282. SRISHAILAM
283. NANDIKOTKUR
284. KURNOOL
285. PANYAM
286. NANDYALA
287. DHONE
288. PATTIKONDA
289. KODMUR
290. ADHONE
291. YAMMIGANUR
292. MANTRALAYAM
293.BANGANAPALLE
294 ALURU
SQL SERVER DATE FORMATES
Problem
There are many instances when dates and times don’t show up at your doorstep in the format you’d like it to be, nor does the output of a query fit the needs of the people viewing it. One option is to format the data in the application itself. Another option is to use the built-in functions SQL Server provides to format the date string for you.
There are many instances when dates and times don’t show up at your doorstep in the format you’d like it to be, nor does the output of a query fit the needs of the people viewing it. One option is to format the data in the application itself. Another option is to use the built-in functions SQL Server provides to format the date string for you.
Solution
SQL Server provides a number of options you can use to format a date/time string. One of the first considerations is the actual date/time needed. The most common is the current date/time using getdate(). This provides the current date and time according to the server providing the date and time. If a universal date/time is needed, then getutcdate() should be used. To change the format of the date, you convert the requested date to a string and specify the format number corresponding to the format needed. Below is a list of formats and an example of the output:
SQL Server provides a number of options you can use to format a date/time string. One of the first considerations is the actual date/time needed. The most common is the current date/time using getdate(). This provides the current date and time according to the server providing the date and time. If a universal date/time is needed, then getutcdate() should be used. To change the format of the date, you convert the requested date to a string and specify the format number corresponding to the format needed. Below is a list of formats and an example of the output:
DATE FORMATS | ||
Format # | Query (current date: 12/30/2006) | Sample |
1 | select convert(varchar, getdate(), 1) | 12/30/06 |
2 | select convert(varchar, getdate(), 2) | 06.12.30 |
3 | select convert(varchar, getdate(), 3) | 30/12/06 |
4 | select convert(varchar, getdate(), 4) | 30.12.06 |
5 | select convert(varchar, getdate(), 5) | 30-12-06 |
6 | select convert(varchar, getdate(), 6) | 30 Dec 06 |
7 | select convert(varchar, getdate(), 7) | Dec 30, 06 |
10 | select convert(varchar, getdate(), 10) | 12-30-06 |
11 | select convert(varchar, getdate(), 11) | 06/12/30 |
101 | select convert(varchar, getdate(), 101) | 12/30/2006 |
102 | select convert(varchar, getdate(), 102) | 2006.12.30 |
103 | select convert(varchar, getdate(), 103) | 30/12/2006 |
104 | select convert(varchar, getdate(), 104) | 30.12.2006 |
105 | select convert(varchar, getdate(), 105) | 30-12-2006 |
106 | select convert(varchar, getdate(), 106) | 30 Dec 2006 |
107 | select convert(varchar, getdate(), 107) | Dec 30, 2006 |
110 | select convert(varchar, getdate(), 110) | 12-30-2006 |
111 | select convert(varchar, getdate(), 111) | 2006/12/30 |
TIME FORMATS | ||
8 or 108 | select convert(varchar, getdate(),  | 00:38:54 |
9 or 109 | select convert(varchar, getdate(), 9) | Dec 30 2006 12:38:54:840AM |
14 or 114 | select convert(varchar, getdate(), 14) | 00:38:54:840 |
You can also format the date or time without dividing characters, as well as concatenate the date and time string:
Sample statement | Output |
select replace(convert(varchar, getdate(),101),’/',”) | 12302006 |
select replace(convert(varchar, getdate(),101),’/',”) + replace(convert(varchar, getdate(),108),’:',”) | 12302006004426 |
Next Steps
- The formats listed above are not inclusive of all formats provided. Experiment with the different format numbers to see what others are available
- These formats can be used for all date/time functions, as well as data being served to clients, so experiment with these data format conversions to see if they can provide data more efficiently
- Find more tips on MSSQLTIPS
How to format datetime & date with century?
Execute the following Microsoft SQL Server T-SQL datetime, date and time formatting scripts in Management Studio Query Editor to demonstrate the usage of the multitude of temporal data formats available and the application of date / datetime functions.– SQL Server T-SQL date and datetime formats – sql date / datetime format
– Date time formats – mssql datetime – sql server date formats – sql dates format
– MSSQL getdate returns current system date and time in standard internal format
– SQL datetime formats with century (YYYY or CCYY format)- sql time format
SELECT convert(varchar, getdate(), 100) – mon dd yyyy hh:mmAM (or PM)
– Oct 2 2010 11:01AM
SELECT convert(varchar, getdate(), 101) – mm/dd/yyyy - 10/02/2010
SELECT convert(varchar, getdate(), 102) – yyyy.mm.dd – 2010.10.02
SELECT convert(varchar, getdate(), 103) – dd/mm/yyyy
SELECT convert(varchar, getdate(), 104) – dd.mm.yyyy
SELECT convert(varchar, getdate(), 105) – dd-mm-yyyy
SELECT convert(varchar, getdate(), 106) – dd mon yyyy
SELECT convert(varchar, getdate(), 107) – mon dd, yyyy
SELECT convert(varchar, getdate(), 108) – hh:mm:ss
SELECT convert(varchar, getdate(), 109) – mon dd yyyy hh:mm:ss:mmmAM (or PM)
– Oct 2 2010 11:02:44:013AM
SELECT convert(varchar, getdate(), 110) – mm-dd-yyyy
SELECT convert(varchar, getdate(), 111) – yyyy/mm/dd
SELECT convert(varchar, getdate(), 112) – yyyymmdd
SELECT convert(varchar, getdate(), 113) – dd mon yyyy hh:mm:ss:mmm
– 02 Oct 2010 11:02:07:577
SELECT convert(varchar, getdate(), 114) – hh:mm:ss:mmm(24h)
SELECT convert(varchar, getdate(), 120) – yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(varchar, getdate(), 121) – yyyy-mm-dd hh:mm:ss.mmm
SELECT convert(varchar, getdate(), 126) – yyyy-mm-ddThh:mm:ss.mmm
– 2010-10-02T10:52:47.513
– Without century (YY) date / datetime conversion – there are exceptions!
SELECT convert(varchar, getdate(), 0) – mon dd yyyy hh:mmAM (or PM)
SELECT convert(varchar, getdate(), 1) – mm/dd/yy
SELECT convert(varchar, getdate(), 2) – yy.mm.dd
SELECT convert(varchar, getdate(), 3) – dd/mm/yy
SELECT convert(varchar, getdate(), 4) – dd.mm.yy
SELECT convert(varchar, getdate(), 5) – dd-mm-yy
SELECT convert(varchar, getdate(), 6) – dd mon yy
SELECT convert(varchar, getdate(), 7) – mon dd, yy
SELECT convert(varchar, getdate(), 8) – hh:mm:ss
SELECT convert(varchar, getdate(), 9) – mon dd yyyy hh:mm:ss:mmmAM (or PM)
SELECT convert(varchar, getdate(), 10) – mm-dd-yy
SELECT convert(varchar, getdate(), 11) – yy/mm/dd
SELECT convert(varchar, getdate(), 12) – yymmdd
SELECT convert(varchar, getdate(), 13) – dd mon yyyy hh:mm:ss:mmm
SELECT convert(varchar, getdate(), 14) – hh:mm:ss:mmm(24h)
SELECT convert(varchar, getdate(), 20) – yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(varchar, getdate(), 21) – yyyy-mm-dd hh:mm:ss.mmm
SELECT convert(varchar, getdate(), 22) – mm/dd/yy hh:mm:ss AM (or PM)
SELECT convert(varchar, getdate(), 23) – yyyy-mm-dd
SELECT convert(varchar, getdate(), 24) – hh:mm:ss
SELECT convert(varchar, getdate(), 25) – yyyy-mm-dd hh:mm:ss.mmm
– SQL create different date styles with t-sql string functions
SELECT replace(convert(varchar, getdate(), 111), ‘/’, ‘ ‘) – yyyy mm dd
SELECT convert(varchar(7), getdate(), 126) – yyyy-mm
SELECT right(convert(varchar, getdate(), 106), 8) – mon yyyy
SELECT substring(convert(varchar, getdate(), 120),6, 11) – mm-dd hh:mm
SELECT substring(convert(varchar, getdate(), 120),6, 11) – mm-dd hh:mm
————
– SQL Server date formatting function – convert datetime to string
————
– SQL datetime functions – SQL date functions – SQL datetime formatting
– SQL Server date formats – sql server date datetime – sql date formatting
– T-SQL convert dates – T-SQL date formats – Transact-SQL date formats
– Formatting dates sql server – sql convert datetime format
CREATE FUNCTION dbo.fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))
RETURNS VARCHAR(32)
AS
BEGIN
DECLARE @StringDate VARCHAR(32)
SET @StringDate = @FormatMask
IF (CHARINDEX (‘YYYY’,@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘YYYY’, DATENAME(YY, @Datetime))
IF (CHARINDEX (‘YY’,@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘YY’, RIGHT(DATENAME(YY, @Datetime),2))
IF (CHARINDEX (‘Month’,@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘Month’, DATENAME(MM, @Datetime))
IF (CHARINDEX (‘MON’,@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)
SET @StringDate = REPLACE(@StringDate, ‘MON’,
LEFT(UPPER(DATENAME(MM, @Datetime)),3))
IF (CHARINDEX (‘Mon’,@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘Mon’, LEFT(DATENAME(MM, @Datetime),3))
IF (CHARINDEX (‘MM’,@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘MM’,
RIGHT(’0′+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))
IF (CHARINDEX (‘M’,@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘M’,
CONVERT(VARCHAR,DATEPART(MM, @Datetime)))
IF (CHARINDEX (‘DD’,@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘DD’,
RIGHT(’0′+DATENAME(DD, @Datetime),2))
IF (CHARINDEX (‘D’,@StringDate) > 0)
SET @StringDate = REPLACE(@StringDate, ‘D’, DATENAME(DD, @Datetime))
RETURN @StringDate
END
GO
– Microsoft SQL Server date format function test
– MSSQL formatting dates – sql datetime date
SELECT dbo.fnFormatDate (getdate(), ‘MM/DD/YYYY’) – 01/03/2012
SELECT dbo.fnFormatDate (getdate(), ‘DD/MM/YYYY’) – 03/01/2012
SELECT dbo.fnFormatDate (getdate(), ‘M/DD/YYYY’) – 1/03/2012
SELECT dbo.fnFormatDate (getdate(), ‘M/D/YYYY’) – 1/3/2012
SELECT dbo.fnFormatDate (getdate(), ‘M/D/YY’) – 1/3/12
SELECT dbo.fnFormatDate (getdate(), ‘MM/DD/YY’) – 01/03/12
SELECT dbo.fnFormatDate (getdate(), ‘MON DD, YYYY’) – JAN 03, 2012
SELECT dbo.fnFormatDate (getdate(), ‘Mon DD, YYYY’) – Jan 03, 2012
SELECT dbo.fnFormatDate (getdate(), ‘Month DD, YYYY’) – January 03, 2012
SELECT dbo.fnFormatDate (getdate(), ‘YYYY/MM/DD’) – 2012/01/03
SELECT dbo.fnFormatDate (getdate(), ‘YYYYMMDD’) – 20120103
SELECT dbo.fnFormatDate (getdate(), ‘YYYY-MM-DD’) – 2012-01-03
– CURRENT_TIMESTAMP returns current system date and time in standard internal format
SELECT dbo.fnFormatDate (CURRENT_TIMESTAMP,‘YY.MM.DD’) – 12.01.03
GO
————
/***** SELECTED SQL DATE/DATETIME FORMATS WITH NAMES *****/
– SQL format datetime – - sql hh mm ss – sql yyyy mm dd
– Default format: Oct 23 2006 10:40AM
SELECT [Default]=CONVERT(varchar,GETDATE(),100)
– US-Style format: 10/23/2006
SELECT [US-Style]=CONVERT(char,GETDATE(),101)
– ANSI format: 2006.10.23
SELECT [ANSI]=CONVERT(char,CURRENT_TIMESTAMP,102)
– UK-Style format: 23/10/2006
SELECT [UK-Style]=CONVERT(char,GETDATE(),103)
– German format: 23.10.2006
SELECT [German]=CONVERT(varchar,GETDATE(),104)
– ISO format: 20061023
SELECT ISO=CONVERT(varchar,GETDATE(),112)
– ISO8601 format: 2010-10-23T19:20:16.003
SELECT [ISO8601]=CONVERT(varchar,GETDATE(),126)
————
– SQL Server datetime formats – Format dates SQL Server 2005 / 2008
– Century date format MM/DD/YYYY usage in a query
– Century date format MM/DD/YYYY usage in a query
SELECT TOP (1)
SalesOrderID,
OrderDate = CONVERT(char(10), OrderDate, 101),
OrderDateTime = OrderDate
FROM AdventureWorks.Sales.SalesOrderHeader
/*
SalesOrderID OrderDate OrderDateTime
43697 07/01/2001 2001-07-01 00:00:00.000
*/
– SQL update datetime column - SQL datetime DATEADD – datetime function
UPDATE Production.Product
SET ModifiedDate=DATEADD(dd,1, ModifiedDate)
WHERE ProductID = 1001
– MM/DD/YY date format - Datetime format sql
SELECT TOP (1)
SalesOrderID,
OrderDate = CONVERT(varchar(8), OrderDate, 1),
OrderDateTime = OrderDate
FROM AdventureWorks.Sales.SalesOrderHeader
ORDER BY SalesOrderID desc
/*
SalesOrderID OrderDate OrderDateTime
75123 07/31/04 2004-07-31 00:00:00.000
*/
————
– SQL convert datetime to char – sql date string concatenation: + (plus) operatorPRINT ‘Style 110: ‘+CONVERT(CHAR(10),GETDATE(),110) – Style 110: 07-10-2012
PRINT ‘Style 111: ‘+CONVERT(CHAR(10),GETDATE(),111) – Style 111: 2012/07/10
PRINT ‘Style 112: ‘+CONVERT(CHAR(8), GETDATE(),112) – Style 112: 20120710
————– Combining different style formats for date & time
– Datetime formats – sql times format – datetime formats sql
DECLARE @Date DATETIME
SET @Date = ’2015-12-22 03:51 PM’
SELECT CONVERT(CHAR(10),@Date,110) + SUBSTRING(CONVERT(varchar,@Date,0),12,8)
– Result: 12-22-2015 3:51PM
– Microsoft SQL Server cast datetime to string
SELECT stringDateTime=CAST (getdate() as varchar)
– Result: Dec 29 2012 3:47AM
————
– SQL Server date and time functions overview
————
– SQL Server CURRENT_TIMESTAMP function
– SQL Server datetime functions
– local NYC – EST – Eastern Standard Time zone
– SQL DATEADD function – SQL DATEDIFF function
SELECT CURRENT_TIMESTAMP – 2012-01-05 07:02:10.577
– SQL Server DATEADD function
SELECT DATEADD(month,2,’2012-12-09′) – 2013-02-09 00:00:00.000
– SQL Server DATEDIFF function
SELECT DATEDIFF(day,’2012-12-09′,’2013-02-09′) – 62
– SQL Server DATENAME function
SELECT DATENAME(month, ’2012-12-09′) – December
SELECT DATENAME(weekday, ’2012-12-09′) – Sunday
– SQL Server DATEPART function
SELECT DATEPART(month, ’2012-12-09′) – 12
– SQL Server DAY function
SELECT DAY(’2012-12-09′) – 9
– SQL Server GETDATE function
– local NYC – EST – Eastern Standard Time zone
SELECT GETDATE() – 2012-01-05 07:02:10.577
– SQL Server GETUTCDATE function
– London – Greenwich Mean Time
SELECT GETUTCDATE() – 2012-01-05 12:02:10.577
– SQL Server MONTH function
SELECT MONTH(’2012-12-09′) – 12
– SQL Server YEAR function
SELECT YEAR(’2012-12-09′) – 2012
————
– T-SQL Date and time function application
– CURRENT_TIMESTAMP and getdate() are the same in T-SQL
————
– T-SQL first day of week and last day of week
SELECT FirstDateOfWeek = dateadd(dd,-DATEPART(dw,GETDATE()) + 1,GETDATE())
SELECT LastDateOfWeek = dateadd(dd,7 - DATEPART(dw,GETDATE()),GETDATE())
– SQL first day of the month
– SQL first date of the month
– SQL first day of current month – 2012-01-01 00:00:00.000
SELECT DATEADD(dd,0,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))
– SQL last day of the month
– SQL last date of the month
– SQL last day of current month – 2012-01-31 00:00:00.000
SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP)+1,0))
– SQL first day of last month
– SQL first day of previous month – 2011-12-01 00:00:00.000
SELECT DATEADD(mm,-1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))
– SQL last day of last month
– SQL last day of previous month – 2011-12-31 00:00:00.000
SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,DATEADD(MM,-1,GETDATE()))+1,0))
– SQL first day of next month – 2012-02-01 00:00:00.000
SELECT DATEADD(mm,1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))
– SQL last day of next month – 2012-02-28 00:00:00.000
SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,DATEADD(MM,1,GETDATE()))+1,0))
GO
– SQL first day of a month – 2012-10-01 00:00:00.000
DECLARE @Date datetime; SET @Date = ’2012-10-23′
SELECT DATEADD(dd,0,DATEADD(mm, DATEDIFF(mm,0,@Date),0))
GO
– SQL last day of a month – 2012-03-31 00:00:00.000
DECLARE @Date datetime; SET @Date = ’2012-03-15′
SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,@Date)+1,0))
GO
GO
– SQL first day of year
– SQL first day of the year - 2012-01-01 00:00:00.000
SELECT DATEADD(yy, DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0)
– SQL last day of year
– SQL last day of the year – 2012-12-31 00:00:00.000
SELECT DATEADD(yy,1, DATEADD(dd, -1, DATEADD(yy,
DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0)))
– SQL last day of last year
– SQL last day of previous year – 2011-12-31 00:00:00.000
SELECT DATEADD(dd,-1,DATEADD(yy,DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0))
GO
GO
– SQL calculate age in years, months, days - Format dates SQL Server 2008
– SQL table-valued function - SQL user-defined function – UDF
– SQL Server age calculation – date difference
USE AdventureWorks2008;
GO
CREATE FUNCTION fnAge (@BirthDate DATETIME)
RETURNS @Age TABLE(Years INT,
Months INT,
Days INT)
AS
BEGIN
DECLARE @EndDate DATETIME, @Anniversary DATETIME
SET @EndDate = Getdate()
SET @Anniversary = Dateadd(yy,Datediff(yy,@BirthDate,@EndDate),@BirthDate)
INSERT @Age
SELECT Datediff(yy,@BirthDate,@EndDate) - (CASE
WHEN @Anniversary > @EndDate THEN 1
ELSE 0
END), 0, 0
UPDATE @Age SET Months = Month(@EndDate - @Anniversary) - 1
UPDATE @Age SET Days = Day(@EndDate - @Anniversary) - 1
RETURN
END
GO
– Test table-valued UDF
SELECT * FROM fnAge(’1956-10-23′)
SELECT * FROM dbo.fnAge(’1956-10-23′)
/* Results
Years Months Days
52 4 1
*/
———-
– SQL date range between
———-
– SQL between dates
USE AdventureWorks;
– SQL between
SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
WHERE OrderDate BETWEEN ’20040301′ AND ’20040315′
– Result: 108
– BETWEEN operator is equivalent to >=…AND….<=
SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
WHERE OrderDate
BETWEEN ’2004-03-01 00:00:00.000′ AND ’2004-03-15 00:00:00.000′
/*
Orders with OrderDates
’2004-03-15 00:00:01.000′ – 1 second after midnight (12:00AM)
’2004-03-15 00:01:00.000′ – 1 minute after midnight
’2004-03-15 01:00:00.000′ – 1 hour after midnight
are not included in the two queries above.
*/
– To include the entire day of 2004-03-15 use the following two solutions
SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
WHERE OrderDate >= ’20040301′ AND OrderDate < ’20040316′
– SQL between with DATE type (SQL Server 2008)
SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
WHERE CONVERT(DATE, OrderDate) BETWEEN ’20040301′ AND ’20040315′
———-
– Non-standard format conversion: 2011 December 14
– SQL datetime to string
SELECT [YYYY Month DD] =
CAST(YEAR(GETDATE()) AS VARCHAR(4))+ ‘ ‘+
DATENAME(MM, GETDATE()) + ‘ ‘ +
CAST(DAY(GETDATE()) AS VARCHAR(2))
– Converting datetime to YYYYMMDDHHMMSS format: 20121214172638
SELECT replace(convert(varchar, getdate(),111),‘/’,”) +
replace(convert(varchar, getdate(),108),‘:’,”)
– Datetime custom format conversion to YYYY_MM_DD
select CurrentDate=rtrim(year(getdate())) + ‘_’ +
right(’0′ + rtrim(month(getdate())),2) + ‘_’ +
right(’0′ + rtrim(day(getdate())),2)
– Converting seconds to HH:MM:SS format
declare @Seconds int
set @Seconds = 10000
select TimeSpan=right(’0′ +rtrim(@Seconds / 3600),2) + ‘:’ +
right(’0′ + rtrim((@Seconds % 3600) / 60),2) + ‘:’ +
right(’0′ + rtrim(@Seconds % 60),2)
– Result: 02:46:40
– Test result
select 2*3600 + 46*60 + 40
– Result: 10000
– Set the time portion of a datetime value to 00:00:00.000
– SQL strip time from date
– SQL strip time from datetime
SELECT CURRENT_TIMESTAMP ,DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 0)
– Results: 2014-01-23 05:35:52.793 2014-01-23 00:00:00.000
/* VALID DATE RANGES FOR DATE/DATETIME DATA TYPES
SMALLDATETIME (4 bytes) date range:
January 1, 1900 through June 6, 2079
DATETIME (8 bytes) date range:
January 1, 1753 through December 31, 9999
DATETIME2 (8 bytes) date range (SQL Server 2008):
January 1,1 AD through December 31, 9999 AD
DATE (3 bytes) date range (SQL Server 2008):
January 1, 1 AD through December 31, 9999 AD
*******/
– Selecting with CONVERT into different styles
– Note: Only Japan & ISO styles can be used in ORDER BY
SELECT TOP(1)
Italy = CONVERT(varchar, OrderDate, 105)
, USA = CONVERT(varchar, OrderDate, 110)
, Japan = CONVERT(varchar, OrderDate, 111)
, ISO = CONVERT(varchar, OrderDate, 112)
FROM AdventureWorks.Purchasing.PurchaseOrderHeader
ORDER BY PurchaseOrderID DESC
/* Results
Italy USA Japan ISO
25-07-2004 07-25-2004 2004/07/25 20040725
*/
– SQL Server convert date to integer
DECLARE @Datetime datetime
SET @Datetime = ’2012-10-23 10:21:05.345′
SELECT DateAsInteger = CAST (CONVERT(varchar,@Datetime,112) as INT)
– Result: 20121023
– SQL Server convert integer to datetime
DECLARE @intDate int
SET @intDate = 20120315
SELECT IntegerToDatetime = CAST(CAST(@intDate as varchar) as datetime)
– Result: 2012-03-15 00:00:00.000
————
– SQL Server CONVERT script applying table INSERT/UPDATE
————
– SQL Server convert date
– Datetime column is converted into date only string column
USE tempdb;
GO
CREATE TABLE sqlConvertDateTime (
DatetimeCol datetime,
DateCol char(8));
INSERT sqlConvertDateTime (DatetimeCol) SELECT GETDATE()
UPDATE sqlConvertDateTime
SET DateCol = CONVERT(char(10), DatetimeCol, 112)
SELECT * FROM sqlConvertDateTime
– SQL Server convert datetime
– The string date column is converted into datetime column
UPDATE sqlConvertDateTime
SET DatetimeCol = CONVERT(Datetime, DateCol, 112)
SELECT * FROM sqlConvertDateTime
– Adding a day to the converted datetime column with DATEADD
UPDATE sqlConvertDateTime
SET DatetimeCol = DATEADD(day, 1, CONVERT(Datetime, DateCol, 112))
SELECT * FROM sqlConvertDateTime
– Equivalent formulation
– SQL Server cast datetime
UPDATE sqlConvertDateTime
SET DatetimeCol = DATEADD(dd, 1, CAST(DateCol AS datetime))
SELECT * FROM sqlConvertDateTime
GO
DROP TABLE sqlConvertDateTime
GO
/* First results
DatetimeCol DateCol
2014-12-25 16:04:15.373 20141225 */
/* Second results:
DatetimeCol DateCol
2014-12-25 00:00:00.000 20141225 */
/* Third results:
DatetimeCol DateCol
2014-12-26 00:00:00.000 20141225 */
————
– SQL month sequence – SQL date sequence generation with table variable
– SQL Server cast string to datetime – SQL Server cast datetime to string
– SQL Server insert default values method
DECLARE @Sequence table (Sequence int identity(1,1))
DECLARE @i int; SET @i = 0
DECLARE @StartDate datetime;
SET @StartDate = CAST(CONVERT(varchar, year(getdate()))+
RIGHT(’0′+convert(varchar,month(getdate())),2) + ’01′ AS DATETIME)
WHILE ( @i < 120)
BEGIN
INSERT @Sequence DEFAULT VALUES
SET @i = @i + 1
END
SELECT MonthSequence = CAST(DATEADD(month, Sequence,@StartDate) AS varchar)
FROM @Sequence
GO
/* Partial results:
MonthSequence
Jan 1 2012 12:00AM
Feb 1 2012 12:00AM
Mar 1 2012 12:00AM
Apr 1 2012 12:00AM
*/
————
————
– SQL Server Server datetime internal storage
– SQL Server datetime formats
– SQL Server datetime formats
————
– SQL Server datetime to hex
SELECT Now=CURRENT_TIMESTAMP, HexNow=CAST(CURRENT_TIMESTAMP AS BINARY(8))
/* Results
Now HexNow
2009-01-02 17:35:59.297 0x00009B850122092D
*/
– SQL Server date part – left 4 bytes – Days since 1900-01-01
SELECT Now=DATEADD(DAY, CONVERT(INT, 0x00009B85), ’19000101′)
GO
– Result: 2009-01-02 00:00:00.000
– SQL time part – right 4 bytes – milliseconds since midnight
– 1000/300 is an adjustment factor
– SQL dateadd to Midnight
SELECT Now=DATEADD(MS, (1000.0/300)* CONVERT(BIGINT, 0x0122092D), ’2009-01-02′)
GO
– Result: 2009-01-02 17:35:59.290
————
————
– String date and datetime date&time columns usage
– SQL Server datetime formats in tables
– SQL Server datetime formats in tables
————
USE tempdb;
SET NOCOUNT ON;
– SQL Server select into table create
SELECT TOP (5)
FullName=convert(nvarchar(50),FirstName+‘ ‘+LastName),
BirthDate = CONVERT(char(8), BirthDate,112),
ModifiedDate = getdate()
INTO Employee
FROM AdventureWorks.HumanResources.Employee e
INNER JOIN AdventureWorks.Person.Contact c
ON c.ContactID = e.ContactID
ORDER BY EmployeeID
GO
– SQL Server alter table
ALTER TABLE Employee ALTER COLUMN FullName nvarchar(50) NOT NULL
GO
ALTER TABLE Employee
ADD CONSTRAINT [PK_Employee] PRIMARY KEY (FullName )
GO
/* Results
Table definition for the Employee table
Note: BirthDate is string date (only)
CREATE TABLE dbo.Employee(
FullName nvarchar(50) NOT NULL PRIMARY KEY,
BirthDate char(8) NULL,
ModifiedDate datetime NOT NULL
)
*/
SELECT * FROM Employee ORDER BY FullName
GO
/* Results
FullName BirthDate ModifiedDate
Guy Gilbert 19720515 2009-01-03 10:10:19.217
Kevin Brown 19770603 2009-01-03 10:10:19.217
Rob Walters 19650123 2009-01-03 10:10:19.217
Roberto Tamburello 19641213 2009-01-03 10:10:19.217
Thierry D’Hers 19490829 2009-01-03 10:10:19.217
*/
– SQL Server age
SELECT FullName, Age = DATEDIFF(YEAR, BirthDate, GETDATE()),
RowMaintenanceDate = CAST (ModifiedDate AS varchar)
FROM Employee ORDER BY FullName
GO
/* Results
FullName Age RowMaintenanceDate
Guy Gilbert 37 Jan 3 2009 10:10AM
Kevin Brown 32 Jan 3 2009 10:10AM
Rob Walters 44 Jan 3 2009 10:10AM
Roberto Tamburello 45 Jan 3 2009 10:10AM
Thierry D’Hers 60 Jan 3 2009 10:10AM
*/
– SQL Server age of Rob Walters on specific dates
– SQL Server string to datetime implicit conversion with DATEADD
SELECT AGE50DATE = DATEADD(YY, 50, ’19650123′)
GO
– Result: 2015-01-23 00:00:00.000
– SQL Server datetime to string, Italian format for ModifiedDate
– SQL Server string to datetime implicit conversion with DATEDIFF
SELECT FullName,
AgeDEC31 = DATEDIFF(YEAR, BirthDate, ’20141231′),
AgeJAN01 = DATEDIFF(YEAR, BirthDate, ’20150101′),
AgeJAN23 = DATEDIFF(YEAR, BirthDate, ’20150123′),
AgeJAN24 = DATEDIFF(YEAR, BirthDate, ’20150124′),
ModDate = CONVERT(varchar, ModifiedDate, 105)
FROM Employee
WHERE FullName = ‘Rob Walters’
ORDER BY FullName
GO
/* Results
Important Note: age increments on Jan 1 (not as commonly calculated)
FullName AgeDEC31 AgeJAN01 AgeJAN23 AgeJAN24 ModDate
Rob Walters 49 50 50 50 03-01-2009
*/
————
– SQL combine integer date & time into datetime
————
– Datetime format sql
– SQL stuff
DECLARE @DateTimeAsINT TABLE ( ID int identity(1,1) primary key,
DateAsINT int,
TimeAsINT int
)
– NOTE: leading zeroes in time is for readability only!
INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 235959)
INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 010204)
INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 002350)
INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 000244)
INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 000050)
INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 000006)
SELECT DateAsINT, TimeAsINT,
CONVERT(datetime, CONVERT(varchar(8), DateAsINT) + ‘ ‘+
STUFF(STUFF ( RIGHT(REPLICATE(’0′, 6) + CONVERT(varchar(6), TimeAsINT), 6),
3, 0, ‘:’), 6, 0, ‘:’)) AS DateTimeValue
FROM @DateTimeAsINT
ORDER BY ID
GO
/* Results
DateAsINT TimeAsINT DateTimeValue
20121023 235959 2012-10-23 23:59:59.000
20121023 10204 2012-10-23 01:02:04.000
20121023 2350 2012-10-23 00:23:50.000
20121023 244 2012-10-23 00:02:44.000
20121023 50 2012-10-23 00:00:50.000
20121023 6 2012-10-23 00:00:06.000
*/
————
– SQL Server string to datetime, implicit conversion with assignment
UPDATE Employee SET ModifiedDate = ’20150123′
WHERE FullName = ‘Rob Walters’
GO
SELECT ModifiedDate FROM Employee WHERE FullName = ‘Rob Walters’
GO
– Result: 2015-01-23 00:00:00.000
/* SQL string date, assemble string date from datetime parts */
– SQL Server cast string to datetime – sql convert string date
– SQL Server number to varchar conversion
– SQL Server leading zeroes for month and day
– SQL Server right string function
UPDATE Employee SET BirthDate =
CONVERT(char(4),YEAR(CAST(’1965-01-23′ as DATETIME)))+
RIGHT(’0′+CONVERT(varchar,MONTH(CAST(’1965-01-23′ as DATETIME))),2)+
RIGHT(’0′+CONVERT(varchar,DAY(CAST(’1965-01-23′ as DATETIME))),2)
WHERE FullName = ‘Rob Walters’
GO
SELECT BirthDate FROM Employee WHERE FullName = ‘Rob Walters’
GO
– Result: 19650123
– Perform cleanup action
DROP TABLE Employee
– SQL nocount
SET NOCOUNT OFF;
GO
————
————
– sql isdate function
————
USE tempdb;
– sql newid – random sort
SELECT top(3) SalesOrderID,
stringOrderDate = CAST (OrderDate AS varchar)
INTO DateValidation
FROM AdventureWorks.Sales.SalesOrderHeader
ORDER BY NEWID()
GO
SELECT * FROM DateValidation
/* Results
SalesOrderID stringOrderDate
56720 Oct 26 2003 12:00AM
73737 Jun 25 2004 12:00AM
70573 May 14 2004 12:00AM
*/
– SQL update with top
UPDATE TOP(1) DateValidation
SET stringOrderDate = ‘Apb 29 2004 12:00AM’
GO
– SQL string to datetime fails without validation
SELECT SalesOrderID, OrderDate = CAST (stringOrderDate as datetime)
FROM DateValidation
GO
/* Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an
out-of-range value.
*/
– sql isdate – filter for valid dates
SELECT SalesOrderID, OrderDate = CAST (stringOrderDate as datetime)
FROM DateValidation
WHERE ISDATE(stringOrderDate) = 1
GO
/* Results
SalesOrderID OrderDate
73737 2004-06-25 00:00:00.000
70573 2004-05-14 00:00:00.000
*/
– SQL drop table
DROP TABLE DateValidation
Go
————
– SELECT between two specified dates – assumption TIME part is 00:00:00.000
————
– SQL datetime between
– SQL select between two dates
SELECT EmployeeID, RateChangeDate
FROM AdventureWorks.HumanResources.EmployeePayHistory
WHERE RateChangeDate >= ’1997-11-01′ AND
RateChangeDate < DATEADD(dd,1,’1998-01-05′)
GO
/* Results
EmployeeID RateChangeDate
3 1997-12-12 00:00:00.000
4 1998-01-05 00:00:00.000
*/
/* Equivalent to
– SQL datetime range
SELECT EmployeeID, RateChangeDate
FROM AdventureWorks.HumanResources.EmployeePayHistory
WHERE RateChangeDate >= ’1997-11-01 00:00:00′ AND
RateChangeDate < ’1998-01-06 00:00:00′
GO
*/
————
– SQL datetime language setting
– SQL Nondeterministic function usage – result varies with language settings
SET LANGUAGE ‘us_english’; –– Jan 12 2015 12:00AM
SELECT US = convert(VARCHAR,convert(DATETIME,’01/12/2015′));
SET LANGUAGE ‘British’; –– Dec 1 2015 12:00AM
SELECT UK = convert(VARCHAR,convert(DATETIME,’01/12/2015′));
SET LANGUAGE ‘German’; –– Dez 1 2015 12:00AM
SET LANGUAGE ‘Deutsch’; –– Dez 1 2015 12:00AM
SELECT Germany = convert(VARCHAR,convert(DATETIME,’01/12/2015′));
SET LANGUAGE ‘French’; –– déc 1 2015 12:00AM
SELECT France = convert(VARCHAR,convert(DATETIME,’01/12/2015′));
SET LANGUAGE ‘Spanish’; –– Dic 1 2015 12:00AM
SELECT Spain = convert(VARCHAR,convert(DATETIME,’01/12/2015′));
SET LANGUAGE ‘Hungarian’; –– jan 12 2015 12:00AM
SELECT Hungary = convert(VARCHAR,convert(DATETIME,’01/12/2015′));
SET LANGUAGE ‘us_english’;
GO————
– SQL Server 2008 T-SQL find next Monday for a given date
DECLARE @DateTime DATETIME = ’2012-12-31′
SELECT NextMondaysDate=DATEADD(dd,(DATEDIFF(dd, 0, @DateTime) / 7 * 7) + 7, 0),
WeekDayName=DATENAME(dw,DATEADD(dd,(DATEDIFF(dd, 0, @DateTime) / 7 * 7) + 7, 0));
/*
NextMondaysDate WeekDayName
2013-01-07 00:00:00.000 Monday
*/
————
————
– Function for Monday dates calculation
————
USE AdventureWorks2008;
GO
– SQL user-defined function
– SQL scalar function – UDF
CREATE FUNCTION fnMondayDate
(@Year INT,
@Month INT,
@MondayOrdinal INT)
RETURNS DATETIME
AS
BEGIN
DECLARE @FirstDayOfMonth CHAR(10),
@SeedDate CHAR(10)
SET @FirstDayOfMonth = convert(VARCHAR,@Year) + ‘-’ + convert(VARCHAR,@Month) + ‘-01′
SET @SeedDate = ’1900-01-01′
RETURN DATEADD(DD,DATEDIFF(DD,@SeedDate,DATEADD(DD,(@MondayOrdinal * 7) - 1,
@FirstDayOfMonth)) / 7 * 7, @SeedDate)
END
GO
– Test Datetime UDF
– Third Monday in Feb, 2015
SELECT dbo.fnMondayDate(2016,2,3)
– 2015-02-16 00:00:00.000
– First Monday of current month
SELECT dbo.fnMondayDate(Year(getdate()),Month(getdate()),1)
Subscribe to:
Posts (Atom)