[mssql] How to get length of Text, NText and Image columns in SQL Server

Problem
There is sometimes a need to figure out the maximum space that is being used by a particular column in your database.  You would initially think that the LEN() function would allow you to do this, but this function does not work on Text, NText or Image data types, so how do you figure out the length of a value in a column that has one of these data types?

SolutionIn addition to the LEN() function, SQL Server also has a DATALENGTH() function.  This function can be used on all data types in your table.

Here is an example of running these commands on an IMAGE data type using the LEN function:

SELECT name, LEN(packagedata)
FROM dbo.sysdtspackages

query results


Here is an example of running these commands on an IMAGE data type using the DATALENGTH function:

SELECT name, DATALENGTH(packagedata)
FROM dbo.sysdtspackages

query results



If you wanted to find out the maximum length used for all of your records you could issue a command such as the following:

SELECT TOP 1 name, DATALENGTH(packagedata)
FROM dbo.sysdtspackages
ORDER BY 2 DESC



That is all there is to it.  Another little function that you probably won't use often, but this is helpful to know it is there when you do need to use it.

[mssql] ^ (Cap) is not the same with SQUARE()

Never use the ^ (cap sign) to replace the SQUARE() function.

SELECT 1024 ^ 2

result: 1026

SELECT SQUARE(1024)

result: 1048576

You see the difference!

[EXCEL] Week Number

Introduction

Many applications and organizations use the week number of a date for some sort of identification, classification, or formatting purpose. However, what constitutes a week number, specifically when does Week 1 begin, is not always the same from one organization or application to another. This page describes worksheet formulas and VBA procedures that you can use to calculate a week number in a number of different manners.

The formulas and functions described on this page work with the following definitions of the first day of week 1 of a year:

• The first day of Week 1 is January of that year, regardless of what day of the week that might be.
• The first day of Week 1 is the first occurrence of some specified day of the week. For example, you may define Week 1 to begin on the first Monday of the year.
• The first day of Week 1 is a specific date. For example, you may define that Week 1 always begins on May 1 of the year.
• The first day of Week 1 is the first occurrence of one day of the week following the first occurrence of another day of the week. For example, you may define that Week 1 begins on the first Monday after the first Thursday of the year.
• The first day of Week 1 is specified by the International Standards Organization (ISO) definition.

Absolute Week Number

This is the simplest method. It assumes that Week 1 begins on January 1, regardless of what day of the week that might be. The formula for this is:

=TRUNC(((StartDate-DATE(YEAR(StartDate),1,0))+6)/7)

where StartDate is the date whose week number is to be returned.

The VBA code for this function is:

Public Function WeekNumberAbsolute(DT As Date) As Long
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' WeekNumberAbsolute
' This returns the week number of the date in DT based on Week 1 starting
' on January 1 of the year of DT, regardless of what day of week that
' might be.
' Formula equivalent:
'       =TRUNC(((DT-DATE(YEAR(DT),1,0))+6)/7)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    WeekNumberAbsolute = Int(((DT - DateSerial(Year(DT), 1, 0)) + 6) / 7)
End Function

Week Number First Day Of Week

This method uses as the first day of Week 1 the first occurrence of a specified day of the week. For example, you might define that Week 1 begins on the first Monday of the year. The formula is:

=TRUNC(((StartDate-DATE(YEAR(StartDate),1,1)+
MOD(DayOfWeek-WEEKDAY(DATE(YEAR(StartDate),1,1)),7))+6)/7)

where StartDate is the whose week number is to be found, and DayOfWeek is the day of week number (1 = Sunday, 2 = Monday, ..., 7 = Saturday).

The VBA code for this function is: 

Public Function WeekNumberFromFirstDayOfWeek(DT As Date, DayOfWeek As VbDayOfWeek) As Long
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' WeekNumberFromFirstDayOfWeek
' This returns the week number of the date DT based on Week 1 starting
' on the first DayOfWeek of the year of DT.
' Formula equivalent:
'       =TRUNC(((DT-DATE(YEAR(DT),1,1)+MOD(DayOfWeek-WEEKDAY(DATE(YEAR(DT),1,1)),7))+6)/7)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
WeekNumberFromFirstDayOfWeek = Int(((DT - DateSerial(Year(DT), 1, 1) + _
    WSMod(DayOfWeek - Weekday(DateSerial(Year(DT), 1, 1)), 7)) + 6) / 7)
End Function

Week Number From Date

This method uses a specific date as the first day of Week 1. The formula is:

=TRUNC(((TestDate-StartDate)+6)/7)+(WEEKDAY(TestDate)=WEEKDAY(StartDate))

where TestDate is the date whose week number is to be calcuatled, and StartDate specifies the first day of Week 1.

The VBA code for this function is:

Public Function WeekNumberFromDate(DT As Date, StartDate As Date) As Long
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' WeekNumberFromDate
' This returns the week number base on Week 1 starting on StartDate.
'       =TRUNC(((DT-StartDate)+6)/7)+(WEEKDAY(DT)=WEEKDAY(StartDate))
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
   WeekNumberFromDate = Int(((DT - StartDate) + 6) / 7) + Abs(Weekday(DT) = Weekday(StartDate))
End Function

Week Number From First Day Of Week Following Another Day Of Week

This methods uses as the first day of Week 1 the first occurrence of one specified day of the week that follows the first occurrence of another day of the week. For example, you might want the first day of week to be the Monday following the first Thursday of the year. The formula is:

=TRUNC(((TestDate-((DATE(YEAR(TestDate),1,1)+
MOD(BaseDayOfWeek-WEEKDAY(DATE(YEAR(TestDate),1,1)),7))+
(MOD(StartDayOfWeek-WEEKDAY((DATE(YEAR(TestDate),1,1)+
MOD(BaseDayOfWeek-WEEKDAY(DATE(YEAR(TestDate),1,1)),7))),7))))+6)/7)+
(WEEKDAY(TestDate)=WEEKDAY(((DATE(YEAR(TestDate),1,1)+
MOD(BaseDayOfWeek-WEEKDAY(DATE(YEAR(TestDate),1,1)),7))+
(MOD(StartDayOfWeek-WEEKDAY((DATE(YEAR(TestDate),1,1)+
MOD(BaseDayOfWeek-WEEKDAY(DATE(YEAR(TestDate),1,1)),7))),7)))))

where TestDate is the date whose week number is to be calculated. StartDayOfWeek is the number of the day of the week after which BaseDate is to be found. BaseDayOfWeek is the weekday number of the day following the first occurrence of BaseDayOfWeek that week 1 should begin. For example, to use the first Monday following the first Thursday of the year, BaseDayOfWeek should be 2 and StartDate should be 5.

The VBA code for this function is: 

Public Function WeekNumberFromDayOfWeekDay(DT As Date, BaseDayOfWeek As VbDayOfWeek, _
    StartDayOfWeek As VbDayOfWeek) As Long
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' WeekNumberFromDayOfWeekDay
' This returns the week number based on Week 1 starting on the first
' StartDayOfWeek following the first BaseDayOfWeek day of week in the
' year of DT. E.g., the week number of 8-March-2009 based on Week 1
' beginning on the first StartDayOfWeek = Wednesday following the first
' BaseDayOfWeek = Sunday of the year 2009. That is, find the first Sunday,
' go to the next Wednesday, and begin Week 1 on that Wednesday.
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim FirstDayOfYear As Long
Dim FirstDayOfYearWeekday As Long

FirstDayOfYear = DateSerial(Year(DT), 1, 1)
FirstDayOfYearWeekday = Weekday(FirstDayOfYear)

WeekNumberFromDayOfWeekDay = Int(((DT - ((FirstDayOfYear + _
        WSMod(BaseDayOfWeek - FirstDayOfYearWeekday, 7)) + _
        (WSMod(StartDayOfWeek - Weekday((FirstDayOfYear + _
        WSMod(BaseDayOfWeek - FirstDayOfYearWeekday, 7))), 7)))) + 6) / 7) + _
        (Weekday(DT) = Weekday(((FirstDayOfYear + WSMod(BaseDayOfWeek - _
        FirstDayOfYearWeekday, 7)) + _
        (WSMod(StartDayOfWeek - Weekday((FirstDayOfYear + _
        WSMod(BaseDayOfWeek - FirstDayOfYearWeekday, 7))), 7)))))

End Function

ISO Week Number

This method uses the International Standards Organization (ISO) definition of a week number. An ISO week always begins on a Monday, and ISO week 1 begins on the Monday of the first week of the year that contains a Thursday. Said differently, ISO week 1 is the first week (beginning on Monday) that has at least four days.

Note that it is possible that the first few days of a year may fall into week 52 or week 53. Although this may seem quite counter intuitive, it follows from the requirements first that all ISO weeks begin on Monday, and second, that the first week must contain a Thursday. For example, the year 2010 begins on Friday, 1-Jan-2010. Since this is later than a Thursday (which is 7-Jan-2010), the week doesn't contains Thursday (or, said another way, the week doesn't have at least four days), the ISO week 1 begins on Monday, 4-January-2010. The day before the start of the year, 31-December-2009, falls in ISO week 53 of the preceeding year, 2009. Rather than deal with the ambiguity of having a day that is in both week 53 and week 0, the 53 carries through until 4-Jan-2010, which as noted before is the Monday of the first week that contains a Thursday (or, equivalently, the first week with four or more days).

The VBA code for calculating an ISO week number is shown below:

Public Function IsoWeekNumber(InDate As Date) As Long
    IsoWeekNumber = DatePart("ww", InDate, vbMonday, vbFirstFourDays)
End Function

The WSMod Function

To maintain a symetry between the VBA and the Formula implementaitons of the various functions, the code uses a function named WSMod that works the way Excel's MOD works. This is different that VBA's Mod operator. 

Private Function WSMod(Number As Double, Divisor As Double) As Double
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' WSMod
' The Excel worksheet function MOD and the VBA Mod operator
' work differently and can return different results under
' certain circumstances. For continuity between the worksheet
' formulas and the VBA code, we use this WSMod function, which
' produces the same result as the Excel MOD worksheet function,
' rather than the VBA Mod operator.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    WSMod = Number - Divisor * Int(Number / Divisor)
End Function

[vb] Terbilang (also for Visual Basic Editor in EXCEL)

Syntax: Terbilang(12345)
Result: Dua Belas Ribu Tiga Ratus Empat Puluh Lima Rupiah

Option Explicit

Function Terbilang(InputAngka As Double) As String
  Dim I%, strMaxDigit$, arrLevel, arrAngka, TempRp$
  
  arrAngka = Array("", "Se", "Dua", "Tiga", "Empat", "Lima", "Enam", "Tujuh", "Delapan", _

"Sembilan", "Sepuluh")
  arrLevel = Array("Triliun ", "Miliar ", "Juta ", "Ribu ", "")
  strMaxDigit = Right("000000000000000" & Abs(Round(InputAngka, 0)), 15)
  For I = 0 To 4
    TempRp = ""
    If Mid(strMaxDigit, 1 + (3 * I), 1) > 0 Then _

TempRp = arrAngka(Mid(strMaxDigit, 1 + (3 * I), 1)) & " Ratus "
    If Mid(strMaxDigit, 2 + (3 * I), 2) < 11 Then
      TempRp = TempRp & arrAngka(Mid(strMaxDigit, 2 + (3 * I), 2))
    ElseIf Mid(strMaxDigit, 2 + (3 * I), 2) < 20 Then
      TempRp = TempRp & arrAngka(Mid(strMaxDigit, 3 + (3 * I), 1)) & " Belas"
    Else
      TempRp = TempRp & arrAngka(Mid(strMaxDigit, 2 + (3 * I), 1)) & " Puluh " & _
               arrAngka(Mid(strMaxDigit, 3 + (3 * I), 1))
    End If
    If Right(TempRp, 1) <> "" Then TempRp = TempRp & " "
    If TempRp <> "" Then TempRp = TempRp & arrLevel(I)
    Terbilang = Terbilang & TempRp
  Next I
  
  If Terbilang <> "" Then
    Terbilang = Replace(Replace(Replace(Replace(Terbilang, _
                        "Se Ribu", "Seribu"), _
                        "Se Ratus", "Seratus"), _
                        "Se Belas", "Sebelas"), _
                        "Se ", "Satu ")
  End If
  
  If Abs(Round(InputAngka, 0)) = 0 Then Terbilang = "Nol "
  If InputAngka < 0 Then Terbilang = "Minus " & Terbilang
End Function