1

I'm trying to subtract a certain amount of seconds from a date for usage elsewhere. However, I need it to be 24hr format. Here is the function I have:

Function getDateTime(df) 'timediff
    getDateTime = DateAdd("S",0 - df,Now)
End Function

It needs to be in the format of 10/5/2015 13:58:32 instead of 10/5/2015 1:58:32 PM

Is there an easy way to convert this or do I need to do string evaluation and manipulation? (if hrs > 12 { etc... })

  • 2
    What DBMS? Come on, you have a 3000+ rep, you should know better. – rory.ap Oct 05 '15 at 16:04
  • I actually meant to include it (thus the parenthesis) I just forgot to go back and add it. :) –  Oct 05 '15 at 16:05
  • @ScottBeeson -- you should definitely tag it – rory.ap Oct 05 '15 at 16:05
  • Actually it was tagged correctly. This is a VBScript question. I edited to clarify. –  Oct 05 '15 at 16:06
  • 2
    The DBMS is relevant, so you need to tag that as well. – rory.ap Oct 05 '15 at 16:07
  • There, now it's not relevant :) –  Oct 05 '15 at 16:08
  • 3
    Okay, that's fine, but remember that in the future. You confused your question by adding superfluous information which drew everyone's attention away from the actual problem. – rory.ap Oct 05 '15 at 16:10
  • Yeah, I tend to do that a lot :( –  Oct 05 '15 at 16:10
  • 1
    `Date` and `Time` are stored as an integer in VBScript so when passing to a DBMS *(which is no longer relevant [apparently](http://stackoverflow.com/questions/32952885/dateadd-seconds-without-am-pm#comment53730742_32952885))* you just need to build a formatted string of the Date using Date Time methods. – user692942 Oct 05 '15 at 16:11
  • [All this](http://stackoverflow.com/a/22575530/692942) applies to what you are trying to do. – user692942 Oct 05 '15 at 16:12
  • @Lankymart: That doesn't work because I cannot then subtract say 813 seconds from `Second(Now())`. It will just give me ~-798 seconds –  Oct 05 '15 at 16:14
  • 1
    No you do your `DateAdd()` first as usual then create a string representation of it to pass to the DBMS. – user692942 Oct 05 '15 at 16:15
  • Oh, of course. /facepalm –  Oct 05 '15 at 16:15
  • Something like `getDateTime = Right("00" & Day(df), 2) & "/" & Right("00" & Month(df), 2) & "/" & Year(df) & " " & Right("00" & Hour(df), 2) & ":" & Right("00" & Minute(df), 2) & ":" & Right("00" & Second(df), 2)`. Personally I'd build a little function to build the string up but you get the idea. – user692942 Oct 05 '15 at 16:19
  • never play with date strings. it relies on regional settings both client and server (dbms) side. totally bad practice. try to use a parameterized query instead. – Kul-Tigin Oct 05 '15 at 16:31
  • 1
    @Kul-Tigin While in an ideal world I would agree with you, this isn't always possible. For example I once had a system that used localised date formats for displaying dates which I then had to build a function to translate backwards and forwards. – user692942 Oct 05 '15 at 16:35

3 Answers3

1

This is my replaced function:

Function getDateTime(df) 'timediff in seconds
    getDateTime = FormatDateTime(DateAdd("S",0 - df,Now),vbGeneralDate)
    getDateTime = Year(getDateTime) & "-" & Month(getDateTime) & "-" & Day(getDateTime)_
    & " " & Hour(getDateTime) & ":" & Minute(getDateTime) & ":" & (Second(getDateTime))
End Function

Thanks to @Lankymart for pointing out the simple fact I was overlooking. Feel free to post and I'll accept yours as an answer.

1

Dealt with this in a another question.

VBScript Date/Time is stored as an integer when you see a particular format that is just the runtime inferring a default string representation of that Date/Time variable (usually using the computers regional settings).

Function FormatDate(df)
  FormatDate = Right("00" & Day(df), 2) & _
    "/" & Right("00" & Month(df), 2) & "/" & Year(df) & _
    " " & Right("00" & Hour(df), 2) & ":" & _
    Right("00" & Minute(df), 2) & ":" & Right("00" & Second(df), 2)
End Function

There is other things you could do that use Left(MonthName(Month(df)), 3) to avoid issues with date format when you have dates like 04/04/2015 for example (as long as the DBMS supports it).

Community
  • 1
  • 1
user692942
  • 16,398
  • 7
  • 76
  • 175
0

Okay, the thing with dates in any programming language (or DBMS for that matter) is that they are just values. How they appear in your IDE, scripting tool, or when queried from a DBMS is irrelevant. They are actually stored the same "under the hood".

If you want them to appear a certain way for a report, on a UI, etc., then you must convert them to a string with a particular format. Again, to reiterate, dates are just numeric values when stored in memory.

When you say "It needs to be in the format of 10/5/2015 13:58:32 instead of 10/5/2015 1:58:32 PM", the latter value is just how your debugger is displaying it (most likely using a ToString() call with a default date format).

rory.ap
  • 34,009
  • 10
  • 83
  • 174
  • Unfortunately this is not true in this use case. The thing I'm outputting the date to cannot only accept very specific formats. –  Oct 05 '15 at 17:06
  • 2
    @ScottBeeson -- You're interpreting it wrong. What I've said in my answer is always true. The thing you're outputting the date to is covered by the "etc." in my statement "If you want them to appear a certain way for a report, on a UI, etc., then you must convert them to a string with a particular format." In a general sense, the point is, you must translate the date value (which is a number in memory) into whatever format you need in order to get it into the other system, whether that's a DBMS, a UI ("system" is used in a more abstract way), etc. – rory.ap Oct 05 '15 at 17:09
  • Okay, I see. So you're arguing semantics and saying I should have specified that I want to "OUTPUT" the date in a certain format? I mean, I appreciate the distinction but it didn't do anything to help me solve the problem :( –  Oct 05 '15 at 17:10
  • I understand, but I didn't know what your problem was at first because it wasn't clear. So my answer was to what I thought your question was really about. – rory.ap Oct 05 '15 at 17:12