Date format in dd/mmm/yyyy when exporting from sql server to excel in classic asp

Question

I have to get the date format as dd-mmm-yyyy i.e., 01-JAN-2012 when exporting data from sql server 2000 to Excel in Classic asp, how can I do that

Answer 1

Take a look at the result of this query

SELECT REPLACE(CONVERT(varchar(50), GETDATE(), 106), ' ', '-')

106 style converts a data to "dd mmm yyyy" format and then you can replace the spaces with whatever delimiter you prefer.

Answer 2

Have a look How to transform a date-string in classic asp This might solve your problem.

Here is another one for reference: How to convert string to datetime format classic asp

Answer 3

try this function (comments are german but the function is not to difficult to understand)

formatDate("%d-%M-%Y", now)

'******************************************************************************************************************
'' @SDESCRIPTION:   formatiert übergebenes Datum anhand eines FormatierungsStrings ähnlich wie in PHP...
'' @PARAM:          format [string]: Formatierungsstring mit fühhrendem %-Zeichen:
''                  %m - Monat mit führender 0 (01 - 12)
''                  %n - Monat ohne führende 0 (1 - 12)
''                  %F - Monatsname (Januar - Dezember)
''                  %M - Abkürzung Monatsname (3 Zeichen) (Jan - Dez)
''                  %d - Tag mit führender 0 (01 - 31)
''                  %j - Tag ohne führende 0 (1 - 31)
''                  %h - Stunde mit führender 0 (01 - 24)
''                  %H - Stunde ohne führende 0 (1 - 24)
''                  %i - Minute mit führender 0 (01 - 60)
''                  %I - Minute ohne führende 0 (1 - 60)
''                  %s - Sekunde mit führender 0 (01 - 60)
''                  %S - Sekunde ohne führende 0 (1 - 60)
''                  %L - Nummers des Wochentages (1 - 7)
''                  %l - Wochentag (Sonntag - Samstag)
''                  %D - Abkürzung des Wochentags (2 Zeichen) (So - Sa)
''                  %U - UNIX Timestamp
''                  %Y - Jahreszahl vierstellig (2006)
''                  %y - Jahreszahl zweistellig (06)
'' @PARAM:          intTimeStamp [long]: entweder ein UNIX timestamp oder ein VB Datum (Now()) oder 0
'' @RETURN:         [string] formatiertes Datum
'******************************************************************************************************************
public function formatDate(format, intTimeStamp)
    Dim unUDate
    Dim monthname(12)
    Dim currentLocale

    ' Monats Array
    monthname(1) = "Januar"
    monthname(2) = "Februar"
    monthname(3) = "März"
    monthname(4) = "April"
    monthname(5) = "Mai"
    monthname(6) = "Juni"
    monthname(7) = "Juli"
    monthname(8) = "August"
    monthname(9) = "September"
    monthname(10) = "Oktober"
    monthname(11) = "November"
    monthname(12) = "Dezember"

    ' GetLocale() liefert aktuelle CID zurück. Diese wird in currentLocal abgelegt.
    currentLocale = GetLocale()

    ' CID wird auf "deutsch" gesetzt
    Call SetLocale("de")

    If isDate(intTimeStamp) Then intTimeStamp = cdate(intTimeStamp)

    ' prüfen, ob intTimeStamp gültig ist.
    ' wenn nicht, wird es als Datum interpretiert und in einen Unix timestamp umgewandelt
    If Not isNumeric(intTimeStamp) Then
        If isDate(intTimeStamp) Then
            'intTimeStamp = timestamp(intTimeStamp)
            intTimeStamp = cdate(intTimeStamp)
            intTimeStamp = DateDiff( "s", "01/01/1970 00:00:00", intTimeStamp )
        Else
            Response.Write "Ungültiges Datum"
            Exit Function
        End If
    End If

    ' wenn intTimeStamp 0 ist, wird aktuelles Datum und Zeit genommen, ansonsten wird aus intTimeStamp ein Datum gemacht
    If intTimeStamp = 0 Then
        unUDate = now()
    Else
        'unUDate = getDate(intTimeStamp)
        unUDate = DateAdd( "s", intTimeStamp, "01/01/1970 00:00:01" )
        unUDate = formatDate( unUDate, vbGeneralDate)
    End If
    unUDate = Trim(unUDate)

    'response.write unUDate
    'response.end

    Dim startM : startM = 1
    Dim startD : startD = InStr(startM, unUDate, ".")+1
    Dim startY : startY = InStr(startD, unUDate, ".")+1
    Dim startHour : startHour = InStr(startY, unUDate, " ")+1
    Dim startMin : startMin = InStr(startHour, unUDate, ":")+1
    Dim startSec : startSec = InStr(startMin+1, unUDate, ":")+1

    Dim dateMonth : dateMonth = Mid(unUDate, startD, ((startY - 1) - startD))
    Dim dateDay : dateDay = Mid(unUDate, 1, ((startD - 1) - 1))
    Dim dateYear : dateYear = Mid(unUDate, startY, 4)

    'response.write unUDate & "<br>" & startHour & "<br>" & startMin & "<br>"
    'response.end

    Dim dateHour : dateHour = Mid(unUDate, startHour, ((startMin - startHour) - 1))
    Dim dateMinute : dateMinute = Mid(unUDate, startMin, 2)
    Dim dateSecond : dateSecond = Mid(unUDate, InStr(startMin, unUDate, ":") + 1, 2)

    format = Replace(format, "%Y", Right(dateYear, 4))
    format = Replace(format, "%y", Right(dateYear, 2))
    format = Replace(format, "%m", dateMonth)
    format = Replace(format, "%n", CInt(dateMonth))
    format = Replace(format, "%F", monthname(CInt(dateMonth)))
    format = Replace(format, "%M", Left(monthname(CInt(dateMonth)), 3))
    format = Replace(format, "%d", dateDay)
    format = Replace(format, "%j", CInt(dateDay))
    format = Replace(format, "%h", Mid(unUDate, startHour, 2))
    format = Replace(format, "%H", CInt(dateHour))
    format = Replace(format, "%i", dateMinute)
    format = Replace(format, "%I", CInt(dateMinute))
    format = Replace(format, "%s", dateSecond)
    format = Replace(format, "%S", CInt(dateSecond))
    format = Replace(format, "%L", WeekDay(unUDate))
    format = Replace(format, "%D", Left(WeekDayName(WeekDay(unUDate)), 2))
    format = Replace(format, "%l", WeekDayName(WeekDay(unUDate)))
    format = Replace(format, "%U", intTimeStamp)

    ' CID wird wieder auf Ursprungswert gesetzt.
    Call SetLocale( currentLocale )
    formatDate = format
End Function