DateAdd Function Cause syntax error converting datetime from character string

Question

When I Execute the query below in an sql server, from visual basic, it runs correctly and returns my data. But when I write fill query in my code it gets an exception

Syntax error converting datetime from character string

I think the error is in the where clause

this is my query:

SELECT        Maint_TaskSheetDetails.TaskId, 
DATEADD(dd, (SELECT        MaintenanceRate
                                FROM            Maint_ItemTasks
                                WHERE        (TaskId = Maint_TaskSheetDetails.TaskId)), 
Maint_TaskSheets.SheetMaintenanceDate) AS Expr1, Maint_ItemTasks_5.TaskName, 
                         Maint_Items.ItemName, All_CostCenter.CostCenterName
FROM            Maint_TaskSheets INNER JOIN
                         Maint_TaskSheetDetails ON Maint_TaskSheets.TaskSheetId = Maint_TaskSheetDetails.TaskSheetId INNER JOIN
                         Maint_ItemTasks AS Maint_ItemTasks_5 ON Maint_TaskSheetDetails.TaskId = Maint_ItemTasks_5.TaskId INNER JOIN
                         Maint_Items ON Maint_TaskSheetDetails.ItemId = Maint_Items.ItemId INNER JOIN
                         All_CostCenter ON Maint_TaskSheets.CostCenterId = All_CostCenter.CostCenterID
**WHERE        (@Date1 <= DATEADD(dd,
                             (SELECT        MaintenanceRate
                                FROM            Maint_ItemTasks AS Maint_ItemTasks_3
                                WHERE        (TaskId = Maint_TaskSheetDetails.TaskId)), Maint_TaskSheets.SheetMaintenanceDate)) AND (@Date2 >= DATEADD(dd,
                             (SELECT        MaintenanceRate
                                FROM            Maint_ItemTasks AS Maint_ItemTasks_2
                                WHERE        (TaskId = Maint_TaskSheetDetails.TaskId)), Maint_TaskSheets.SheetMaintenanceDate))**
UNION ALL
SELECT        Maint_ItemTaskDetails.TaskId, DATEADD(dd,
                             (SELECT        MaintenanceRate
                                FROM            Maint_ItemTasks AS Maint_ItemTasks_1
                                WHERE        (TaskId = Maint_ItemTaskDetails.TaskId)), Maint_ItemTaskDetails.MaintenanceStartingDate) AS Expr1, Maint_ItemTasks_4.TaskName, 
                         Maint_Items_1.ItemName, All_CostCenter_1.CostCenterName
FROM            Maint_ItemTaskDetails INNER JOIN
                         Maint_ItemTasks AS Maint_ItemTasks_4 ON Maint_ItemTaskDetails.TaskId = Maint_ItemTasks_4.TaskId INNER JOIN
                         Maint_Items AS Maint_Items_1 ON Maint_ItemTasks_4.ItemId = Maint_Items_1.ItemId INNER JOIN
                         All_CostCenter AS All_CostCenter_1 ON Maint_ItemTaskDetails.CostCenterId = All_CostCenter_1.CostCenterID
**WHERE        (@Date1 <= DATEADD(dd,
                             (SELECT        MaintenanceRate
                                FROM            Maint_ItemTasks AS Maint_ItemTasks_1
                                WHERE        (TaskId = Maint_ItemTaskDetails.TaskId)), Maint_ItemTaskDetails.MaintenanceStartingDate)) AND (@Date2 >= DATEADD(dd,
                             (SELECT        MaintenanceRate
                                FROM            Maint_ItemTasks AS Maint_ItemTasks_1
                                WHERE        (TaskId = Maint_ItemTaskDetails.TaskId)), Maint_ItemTaskDetails.MaintenanceStartingDate))** AND (Maint_ItemTaskDetails.TaskId NOT IN
                             (SELECT        Maint_TaskSheetDetails_1.TaskId
                                FROM            Maint_TaskSheetDetails AS Maint_TaskSheetDetails_1 INNER JOIN
                                                         Maint_TaskSheets AS Maint_TaskSheets_1 ON Maint_TaskSheetDetails_1.TaskSheetId = Maint_TaskSheets_1.TaskSheetId))

Are your dates actually stored as a datetime, and is your date variables of type date or datetime? — S3S
– S3S, Commented Jul 10, 2016 at 13:50
yes, when i use fill function in my code..date1 and date2 entered as varchar (string) — Rania Fathy
– Rania Fathy, Commented Jul 11, 2016 at 9:32
In SQL you can't add days to varchar so you'll need to cast(date1 as date) or use convert() — S3S
– S3S, Commented Jul 11, 2016 at 11:12

S3S · Accepted Answer · 2016-07-11 11:16:17Z

1

Try casting or converting your date1 and date2 like so.

CAST(@date1 as DATE)

You could also use CONVERT() for specific formats.

answered Jul 11, 2016 at 11:16

S3S

25.2k5 gold badges29 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Rania Fathy Over a year ago

Cast AS Date does not work in sql server 2005, i used DateTime instead , but Unfortunately it gives me the same error

S3S Over a year ago

Two questions. Is maintenanceStartDate a datetime or varchar field? And two, what format are you entering your date for date1 and date2

S3S Over a year ago

Glad to help! Which was it?

Rania Fathy Over a year ago

wow, i solved it, thanks a lot for helping me , i removed all cast functions in my code , otherwise it give me the same error when i execute query from it is table adapter, but when i choose preview data i found that date1 and date2 datatype are date time instead of string,and it works correctly when i run my project, thanks again

Collectives™ on Stack Overflow

DateAdd Function Cause syntax error converting datetime from character string

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related