Date issue in vb script

Question

My issue is when I retrieve date back it gives me in this format:

lastseenstatus=rsprefobj("lastseentstamp")

19-07-2014 15:31:32

I want it in 7/19/2014 3:31:32 PM format with AM/PM intact.

Please help..

This is nothing but a **display** or formatting issue. SQL Server up to 2008 R2 has [`CAST` and `CONVERT`](http://msdn.microsoft.com/en-us/library/ms187928.aspx) which offer various formatting options, and SQL Server 2012 and newer have the [`FORMAT`](http://msdn.microsoft.com/en-us/library/hh213505.aspx) function which allows formatting to your liking — marc_s, Jul 22 '14 at 18:37

score 3 · Accepted Answer · edited May 23 '17 at 11:58

First and foremost you need to determine the data type of rsprefobj("lastseentstamp"):

MsgBox TypeName(rsprefobj("lastseentstamp"))

If it's a string, you need to convert it to a datetime value first:

lastseenstatus = CDate(rsprefobj("lastseentstamp"))

If you want the date formatted according to the system's regional settings, use the FormatDateTime() function as @John suggested:

MsgBox FormatDateTime(lastseenstatus)

If you need a distinct date format regardless of the system's regional settings you have to either build the formatted string yourself:

Function LPad(v) : LPad = Right("00" & v, 2) : End Function

Function FormatDate(d)
  formattedDate = Month(d) & "/" & LPad(Day(d)) & "/" & Year(d) & " " & _
                  ((Hour(d) + 23) Mod 12 + 1) & ":" & LPad(Minute(d)) & ":" & _
                  LPad(Second(d))

  If Hour(d) < 12 Then
    formattedDate = formattedDate & " AM"
  Else
    formattedDate = formattedDate & " PM"
  End If

  FormatDate = formattedDate
End Function

MsgBox FormatDate(lastseenstatus)

or use the .Net StringBuilder class:

Set sb = CreateObject("System.Text.StringBuilder")
sb.AppendFormat "{0:M\/dd\/yyyy h:mm:ss tt}", lastseenstatus

MsgBox sb.ToString()

In my tests I wasn't able to get the tt format specifier to work, though, so you may have to resort to something like this:

Set sb = CreateObject("System.Text.StringBuilder")

If Hour(lastseenstatus) < 12 Then
  am_pm = "AM"
Else
  am_pm = "PM"
End If

sb.AppendFormat_5 Nothing, "{0:M\/dd\/yyyy h:mm:ss} {1}", _
  Array(lastseenstatus, am_pm)

MsgBox sb.ToString()

score 0 · Answer 2 · answered Jul 23 '14 at 00:18

0

I'm assuming you are using VBScript and not VB.NET like you have tagged.

Use FormatDateTime(lastseenstatus).

That should give you the format "2/16/2010 1:45:00 PM".

answered Jul 23 '14 at 00:18

johna

10,540
14
47
72

`FormatDateTime()` will format the given date according to the system's regional settings, which may or may not be the desired format. – Ansgar Wiechers Jul 23 '14 at 08:02

Date issue in vb script

2 Answers2

Linked