- select SERVERPROPERTY('ProductLevel') as 'Product Level'
- select SERVERPROPERTY('ProductUpdateLevel') as 'ProductUpdate Level'
Get Number of Mon/Tue/Wed..Fri between two datesdeclare @from datetime= '3/1/2013'
declare @to datetime = '3/31/2013'
select
datediff(day, -7, @to)/7-datediff(day, -6, @from)/7 AS MON,
datediff(day, -6, @to)/7-datediff(day, -5, @from)/7 AS TUE,
datediff(day, -5, @to)/7-datediff(day, -4, @from)/7 AS WED,
datediff(day, -4, @to)/7-datediff(day, -3, @from)/7 AS THU,
datediff(day, -3, @to)/7-datediff(day, -2, @from)/7 AS FRI,
datediff(day, -2, @to)/7-datediff(day, -1, @from)/7 AS SAT,
datediff(day, -1, @to)/7-datediff(day, 0, @from)/7 AS SUNIf you have trouble converting a date column in SQL Server to any of the desired format, here you go..
I am listing all the major formats below and its corresponding queries
| Format | Query |
| mm/dd/yy - USA Format | select convert(varchar, getdate(), 1) |
| yy.mm.dd - ANSI Format | select convert(varchar, getdate(), 2) |
| dd/mm/yy - British/FrenchFormat | select convert(varchar, getdate(), 3) |
| dd.mm.yy - German Format | select convert(varchar, getdate(), 4) |
| dd-mm-yy - ItalianFormat | select convert(varchar, getdate(), 5) |
| dd mon yy | select convert(varchar, getdate(), 6) |
| Mon dd, yy | select convert(varchar, getdate(), 7) |
| mm-dd-yy - USA Format | select convert(varchar, getdate(), 10) |
| yy/mm/dd - JAPAN Format | select convert(varchar, getdate(), 11) |
| yymmdd - ISO | select convert(varchar, getdate(), 12) |
| mon dd yyyy hh:miAM (or PM) | select convert(varchar, getdate(), 100) |
| mm/dd/yyyy | select convert(varchar, getdate(), 101) |
| yyyy.mm.dd | select convert(varchar, getdate(), 102) |
| dd/mm/yyyy | select convert(varchar, getdate(), 103) |
| dd.mm.yyyy | select convert(varchar, getdate(), 104) |
| dd-mm-yyyy | select convert(varchar, getdate(), 105) |
| dd mon yyyy | select convert(varchar, getdate(), 106) |
| Mon dd, yyyy | select convert(varchar, getdate(), 107) |
| hh:mm:ss | select convert(varchar, getdate(), 108) |
| Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM) | select convert(varchar, getdate(), 109) |
| mm-dd-yyyy | select convert(varchar, getdate(), 110) |
| yyyy/mm/dd | select convert(varchar, getdate(), 111) |
| yyyymmdd | select convert(varchar, getdate(), 112) |
| Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h) | select convert(varchar, getdate(), 113) or select convert(varchar, getdate(), 13) |
| hh:mi:ss:mmm(24h) | select convert(varchar, getdate(), 114) |
