Skip to Content
0

How to convert string to date and time

Mar 06, 2017 at 07:15 PM

154

avatar image

I checked the question and answer section and the archives however I'm not finding my answer.

I'm wanting to convert a string to a date and time. It looks like ToDate won't convert the time portion? I'm using Webi so this function in under the date & time folder but definition doesn't mention time. I also don't see another function specific to time.

My string is

01-02-2016 20:20:43 This gives an #error using formula below.

01-02-2017 02:16:23 this converts to

2/1/17 using the following formula :

= ToDate ( [Event DateStamp] ; "dd-MM-yyyy hh:mm:ss" )

I believe that our db is oracle 11g

The formula doesn't appear to work the same on the two different string values above? In one case it appears to convert the date of one of the strings and none of the time values in either case.

I used oracle format from this link but nothing appears to work:

https://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements004.htm#SQLRF00212

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Jyothirmayee A Mar 06, 2017 at 07:30 PM
0

Hi,

What is your current format of the date; 01-02-2016 20:20:43(MM/dd/yyyy hh:mm:ss OR dd/MM/yyyy hh:mm:ss)?.

The Todate() is the right function to change format from string to Date.

Can you try:

= ToDate ( [Event DateStamp] ; "MM-dd-yyyy hh:mm:ss" )

Thanks,

Jothi

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thank you for your help Jothi! I'm not having an issue with the date portion just the time portion. Currently :

= ToDate ( [Event DateStamp] ; "dd-MM-yyyy HH:mm:ss" )

provides the date portion but no time values.
0
AMIT KUMAR
Mar 06, 2017 at 07:35 PM
0

use this.

= ToDate ( [Event DateStamp] ; "dd-MM-yyyy HH:mm:ss" )

Your time is in 24 hours format so you need to use HH in caps.

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Thank you Amit for your support! I did as you suggested however the result was that I no longer had any errors and I now have a date for every piece of data, however, I still don't have any time values. I thought that when the time was in 24 hour format, the string would be something like HH24 or hh24 which I've attempted. I still don't have a time value.

Thanks Scott

0