topic Re: Date Conversion Problem in QlikView

Date Conversion Problem

khasimvali85 — Thu, 25 Sep 2014 10:05:57 GMT

Hi Community,

Can any body solve this Error when i connect to data base Table showing error like

''ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Conversion failed when converting date and/or time from character string.''

I Change Given format below

,Convert(Varchar,[Date_Field],103) as Date_Field is it right?

Thanks in Advance

Regards,

Khasim.

Re: Date Conversion Problem

ashfaq_haseeb — Thu, 25 Sep 2014 10:10:12 GMT

Hi,

Can you paste your script here?

you can try something like this

load

f1,

Date(floor(YourDateField)) as Date,

f3;

sql select * from table.

Regards

ASHFAQ

Re: Date Conversion Problem

rubenmarin — Thu, 25 Sep 2014 10:12:51 GMT

Hi, Can you try?:

Convert(datetime,[Date_Field],103) as Date_Field

Re: Date Conversion Problem

MK_QSL — Thu, 25 Sep 2014 10:13:22 GMT

Try

convert(varchar(10),[Date_Field],103) as Date_Field

Re: Date Conversion Problem

crusader_ — Thu, 25 Sep 2014 10:14:53 GMT

Hi,

You are trying to use specific MSSQL convert() function in your SQL statement, it's not quite right.

Try Ashfaq's solution to solve your problem.

Regards,

Andrei

Re: Date Conversion Problem

khasimvali85 — Thu, 25 Sep 2014 10:21:15 GMT

HI Ruben,

Thanks for Reply but its not working

Regards,

Khasim

Re: Date Conversion Problem

khasimvali85 — Thu, 25 Sep 2014 10:24:58 GMT

Hi Ashfaq,

My Script is

Calls:

SELECT [Complaint_ID]

,Convert(Varchar,[complaint_date],103) as complaint_date

,[complaint_no]

,[count]

,Convert(datetime,[Reminder_Date],103) as Reminder_Date

,[NewAppointment]

Re: Date Conversion Problem

ashfaq_haseeb — Thu, 25 Sep 2014 10:27:43 GMT

Hi,

From where you are reading this SQL or QVD?

regards

ASHFAQ

Re: Date Conversion Problem

khasimvali85 — Thu, 25 Sep 2014 10:30:23 GMT

Hi,

From SQL

Re: Date Conversion Problem

ashfaq_haseeb — Thu, 25 Sep 2014 10:30:51 GMT

If it is qvd try like this

Calls:

SELECT [Complaint_ID]

,date(floor([complaint_date])) as complaint_date

,[complaint_no]

,[count]

,date(floor([Reminder_Date])) as Reminder_Date

,[NewAppointment]

Re: Date Conversion Problem

ashfaq_haseeb — Thu, 25 Sep 2014 10:35:22 GMT

If SQL try below

Calls:

SELECT [Complaint_ID]

,Convert(Varchar,[complaint_date],103) as complaint_date

,[complaint_no]

,[count]

,Convert(datetime,[Reminder_Date],103) as Reminder_Date

,[NewAppointment]

Re: Date Conversion Problem

crusader_ — Thu, 25 Sep 2014 10:35:35 GMT

Hi,

Ashfaq suggested you right solution in the very first post.

Table:

LOAD

[Complaint_ID]

,date(floor([complaint_date])) as complaint_date

,[complaint_no]

,[count]

,date(floor([Reminder_Date])) as Reminder_Date

,[NewAppointment]

,[Comment]

;

SQL SELECT

[Complaint_ID]

,[complaint_date]

,[complaint_no]

,[count]

,[Reminder_Date]

,[NewAppointment]

Re: Date Conversion Problem

khasimvali85 — Thu, 25 Sep 2014 10:36:22 GMT

Hi Ashfaq,

i am fetching from DataBase but why we are using Select Command?

In QVD fetching data shows Load Command right? previously with given conversion format its working fine and fetched data exact.

Regards,

Khasim.

Re: Date Conversion Problem

rubenmarin — Thu, 25 Sep 2014 10:40:32 GMT

OK, It's solved now? If not, can you provide some sample date to see what conversion is needed?

If it helps, MSSQL convert statement is:

Syntax for CONVERT:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

expression

Is any valid expression.

data_type

Is the target data type. This includes xml, bigint, and sql_variant. Alias data types cannot be used.

length

Is an optional integer that specifies the length of the target data type. The default value is 30.

style

Is an integer expression that specifies how the CONVERT function is to translate expression. If style is NULL, NULL is returned. The range is determined by data_type. For more information, see the Remarks section.

(103 is dd/mm/yyyy format)

Re: Date Conversion Problem

ashfaq_haseeb — Thu, 25 Sep 2014 10:41:59 GMT

Khasimvali Shaik wrote:

Hi Ashfaq,

My Script is

Calls:

SELECT [Complaint_ID]

      ,Convert(Varchar,[complaint_date],103) as complaint_date

      ,[complaint_no]

      ,[count]

      ,Convert(datetime,[Reminder_Date],103) as Reminder_Date

      ,[NewAppointment]

      ,[Comment]

From Calls;

Regards

Khasim.

Hi,

Check SQL part is missing here, you didn't provide complete script

try this and let me know

Calls:

SELECT [Complaint_ID]

,Convert(Varchar,[complaint_date],103) as complaint_date

,[complaint_no]

,[count]

,Convert(datetime,[Reminder_Date],103) as Reminder_Date

,[NewAppointment]

Re: Date Conversion Problem

ashfaq_haseeb — Thu, 25 Sep 2014 10:44:59 GMT

Hi,

If it working previously

Try below SQL part was missing.

Calls:

SQL SELECT [Complaint_ID]

,Convert(Varchar,[complaint_date],103) as complaint_date

,[complaint_no]

,[count]

,Convert(datetime,[Reminder_Date],103) as Reminder_Date

,[NewAppointment]

Re: Date Conversion Problem

maxgro — Thu, 25 Sep 2014 10:53:53 GMT

after some (a lot of) test

for me this works on a sql server database

when I remove the bold part I get an error

table:

sql select Convert(Varchar(10), dat_data, 103) as field1 from d003_date;

Re: Date Conversion Problem

khasimvali85 — Thu, 25 Sep 2014 11:52:07 GMT

Hi Ashfaq,

without conversion format also working and also SQL SELECT Statement also working.

but why when using conversion(varchar,[dateField],103) as dateField not working.

showing same error.

Re: Date Conversion Problem

ashfaq_haseeb — Thu, 25 Sep 2014 11:55:12 GMT

Try this now

Calls:

SELECT [Complaint_ID]

,date(floor([complaint_date])) as complaint_date

,[complaint_no]

,[count]

date(floor([Reminder_Date])) as Reminder_Date

,[NewAppointment]

Re: Date Conversion Problem

ashfaq_haseeb — Thu, 25 Sep 2014 17:33:07 GMT

Like this

Calls:

load [Complaint_ID]

,date(floor([complaint_date])) as complaint_date

,[complaint_no]

,[count]

,date(floor([Reminder_Date])) as Reminder_Date

,[NewAppointment]