Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I havent run into this before maybe you have. I have a table that gives me the creation date of the record. However, there are some date format and some number format. Any suggestions on where to lookto fix this?
COMPLAINT_NUMBER | COMPLAINT_ANALYST | COMPLAINT_STATUS | COMPLAINT_AGE | Product_Name_Comp | Product_Brand_Comp | Site_Complaint | CREATION_DATE | |
6 | ||||||||
CC-00053479 | Bob Smith | Review | 52 | Product 1 | Disney | NoSite | 8/24/2015 | 1 |
CC-00053530 | Bob Smith | Review | 52 | Product 2 | Goofy | NoSite | 8/24/2015 | 1 |
CC-00054168 | Bob Smith | Review | 49 | Product 3 | Mickey | NoSite | 42243 | 1 |
CC-00054507 | Bob Smith | Review | 45 | Product 4 | Disney | NoSite | 8/31/2015 | 1 |
CC-00054909 | Bob Smith | Review | 43 | Product 5 | Goofy | NoSite | 42249 | 1 |
CC-00054959 | Bob Smith | Review | 43 | Product 6 | Mickey | NoSite | 42249 | 1 |
Hi Leo,
Try using the TRIM() function -> Date(Trim(SubField([Date Originated],' ',1)),'M/D/YYYY') AS CREATION_DATE,
I bet there are spaces in the data values causing QV to interpret the number as text rather than a date #
very curious
I would define the dateformat under document Settings tab number
define Creation Date as dateformat with yourrequired date Format
does the data Comes from same source?
in the script, try putting the Date() function around the field.
Date(CreationDate) as Date
Yes, the data comes from the same place, it is related to a calendar, the code looks like this,
SubField([Date Originated],' ',1) AS CREATION_DATE,
Should it look like..
SubField(date([Date Originated]),' ',1) AS CREATION_DATE,
it's a good idea to define the format
but it should be like this:
date(SubField([Date Originated]),' ',1)) AS CREATION_DATE,
Hi Leo,
Try using the TRIM() function -> Date(Trim(SubField([Date Originated],' ',1)),'M/D/YYYY') AS CREATION_DATE,
I bet there are spaces in the data values causing QV to interpret the number as text rather than a date #
maybe also possible:
Date#(SubField([Date Originated],' ',1),'M/DD/YYYY') as CREATION_DATE,
or
Date(Date#(SubField([Date Originated],' ',1),'M/DD/YYYY')) as CREATION_DATE,
to convert it to your default DateFormat.
Or assuming your [Date Originated] field in fact is a timestamp (because you only use the part before the first blank):
Timestamp#([Date Originated],'M/DD/YYYY hh:mm:ss') as CREATION_DATE_TIME,
and in a preceding load
DayName(CREATION_DATE_TIME) as CREATION_DATE
to get both the timestamp and a date.
hope this helps
regards
Marco
that worked, thank you