Converting string date to actual date format

Toad Data Point
1

Hello.

I’m a bit green with SQL and currently using Toad for data analysts. I don’t believe I asked the correct question before or didn’t give accurate information.

I am trying to pull data for the date ranges from 12-5-2011 to 01-25-2012. The date column that I am pulling from has a format that shows as “1,327,512,651. or 1327512651(not sure of format name)” and exports to excel in the same format. I would like to pull the data with the date column showing a dd-mon-yyyy format in a column and another column showing a hh:mm format so I can calculate average time in hours. Can anyone assist?

Thank you.

2

Answer:

When data has the date as a number “text” string that is being converted with CONVERT_DATE function, the function has to be in front of each identifying field i.e.

SELECT SVC_CHANGE.HOUSE_ID_
, xxxdunion.COMCAST_CONVERT_DATE(SVC_CHANGE.CREATE_DATE), SVC_CHANGE.CATEGORY,
SVC_CHANGE.ADDRESS
FROM SVC_CHANGE
WHERE xxxdunion.COMCAST_CONVERT_DATE(create_date) >= trunc (sysdate) -7

Returns "1,327,512,651. or 1327512651 format to 1/31/2012 2:33:09 PM format.

1 Like