convertion of date

[suvbin]

Hi , 

How to convert the date 19990701 to YYYY-MM-DD .  Please give me alternate ways.

Thanks.

[Anonymous]

If you are fetching data from Sql server then try this:-

Load Date(Date#(yourfieldName,'YYYYMMDD'),'YYYY-MM-DD') as Date;

SQL select Date from (source);

[john_wang]

Hello @Anonymous ,

From the article path seems this is a question for Qlik Replicate.

Hello @suvbin ,

Are you able to confirm this is QDA product or QDI product question?

thanks,

John.

[suvbin]

Thank you for the response  john. But the date is not in  YYYY-MM-DD format.  So could you please let me know how can we perform transformation for the same without using substr.

[john_wang]

Hello @suvbin ,

Seems I still not sure I get the question well... anyway, from MS Docs the default format of DATE is Default string literal format YYYY-MM-DD . Or you are meaning format a string to DATE without SUBSTR? then maybe CAST and CONVERT helps? A sample:

Regards,

John.

[suvbin]

Hi Jhon, 

Appreciate your help and Sorry for the confusion, i was asking , in Qlik replicate itself , how we can do the same transformation, not in SQL server studio.  Are there any functions available to do this, without using the substr.

Again sorry for the confusion.

Thanks in advance.

[john_wang]

Hello @suvbin 

We're glad to help, no worries. In general Replicate do the data type conversion implicitly if necessary, sometimes we need special data type conversion explicitly by adding transformation. And, there are even some data type implicit conversion by database itself. All the above conversions take place between source and target DBs. We need not to care about what's the internal 'format' inside Replicate, it's not important for the end to end replication.

Please let us know below information to fully understand the request if you need additional assistance:

1- source side database type

      [SQL Server]

2- target side database type

3- source side column type

      [DATE] (sample input '20230226')

4- target side column type

5- what's the expected result in target side, or other detailed requests

Regards,

John.

[suvbin]

1- source side database type

      [SQL Server]

2- target side database type

3- source side column type

      [DATE] (sample input '20230226')

4- target side column type

Date (output : YYYY-MM-DD)

[neerajthakur]

Hi @suvbin Try This

Share screenshots of error if any for better understanding, share sample code where you are applying this.

[Heinvandenheuvel]

It appears like you want to do this with a transformation inside Replicate using a (SQLlite) function.

The input you shows looks like a string, not a date. There are functions (strftime) to convert a date/time to almost any type of string, but none the other way to finterpret/parse a date-time in a specified format

Assuming the input is a string you need to pick it apart and construct a valid date/time. For example:

date( substr($x,1,4) ||'-'|| substr($x,5,2) ||'-'|| substr($x,7,2))

Does that help?

Hein.