Pull out a single date in field that contains DateHour as 'YYYYMMDDHH' in Where Statement

[mp802377]

I am looking to pull out a single date in field that contains DateHour as 'YYYYMMDDHH' in Where Statement

I need to pull data from a massive dataset.
In the past, with data this large, I created a generator that looked at my config file to find vNumberOfDays. It then created QVDs based on number of days. Each time it ran, it would find the date of the last QVD (FOR Each vFile in FileList) and run for the current day, plus previous days missing, then of course save for each day in the date.

Using the variables below:
LET vDateList = vDateList_forPull;
FOR Each vDate in $(vDateList)

and in the Where statement:
Where datehour = '$(vDate)';

The problem I have run into, with this new dataset, the Date field is a combo date/hour. The date/hour format is as 'YYYYMMDDHH (example 2024050200)
Again, this is a massive dataset. I only want to pull one day at a time and store each day QVD in a folder. Then the aggregator comes in and says, pull this many days and pulls the data together in one large QVD. I have control over it. And there are times, they find they have an issue with the data itself. I can easily delete a few days and re-run. Saving once more a QVD for each day.

How would I

LOAD
*;
SQL SELECT
*
FROM HIVE.`mytable`
Where to_date(datehour) = '$(vDate)'  // '$(vDate)' is set as 20240504

And it didn't work.

On the Main tab decided to change the DateFormat and TimestampFormat to :
SET DateFormat='YYYYMMDDHH';
SET TimestampFormat='YYYYMMDDHH';

That didn't help. It doesn't pull in any data, and I know for certain there is data available for that date.

[brunobertels]

Hi 

may be create a new field from the existing field with this format YYYYMMDDHH to this one : 

load

... 

date(date#(Myfield,'YYYYMMDDHH'),'YYYYMMDD') as MyNewDATE,

then use this field MyNewDate in your variable and where clause 

hope it helps 

[mp802377]

No, but thank you. That would cause me to pull in all the data from the massive field, when I just want one day.

[brunobertels · Accepted solution]

Hi 

Well ok , hopping i did understood well try this :

assuming in your field datehour all the cell have the same value for date 2024050200

Let vDate = left(FieldValue('datehour',1),8);

or 

Let vDate = left(peek('datehour',0,'yourtable'),8)

[mp802377]

Yes, the left() worked. I had just thought of that and tried it. Something so simple. I overthought, that is for sure.