In SQL server 2008 there is one datatype present called “time”. So when we create a column with this datatype, generally it store time in 24 hours format. At the time of selecting also we get that 24 hours time. Using following query helps in getting time in 12 hours format(AM/PM format).
EX: SELECT CONVERT(VARCHAR(20),CLASS_START_TIME,109) FROM TABLE_NAME
O/P– 7:00:00PM, 10:15:00AM etc. (hh:min:sec format)
EX:SELECT CONVERT(VARCHAR(20),CLASS_START_TIME,100) FROM TABLE_NAME O/P– 7:00PM, 10:15AM (hh:min format)
2. How to show only last 2 digit of year while selecting any date
In SQL Server 2008 another one datatype is present called “date” which only stores the date. While selecting date we generally get date in MM/DD/YYYY format. Using following query we can get date in MM/DD/YY format only.
EX: SELECT CONVERT(VARCHAR, DATE_OF_BIRTH,1) FROM TABLE_NAME
3. How to get day of a particular date
Sometimes we need date and its corresponding day.Following query helps us in achieving that.
EX: SELECT DATENAME(WEEKDAY, CLASS_START_DATE) FROM TABLE_NAME