Date Conversion Problem

[khasimvali85]

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.

[ashfaq_haseeb]

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]

      ,

[crusader_]

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]

      ,

[khasimvali85]

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.

[rubenmarin]

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)

[ashfaq_haseeb]

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]

      ,

[ashfaq_haseeb]

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]

      ,

[maxgro]

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;

[khasimvali85]

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.

[ashfaq_haseeb]

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]

      ,

[ashfaq_haseeb · Accepted solution]

Like this

Calls:

load  [Complaint_ID]

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

      ,[complaint_no]

      ,[count]

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

      ,[NewAppointment]

      ,