Need help on converting date and/or time from char

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help on converting date and/or time from char

Author

Topic

Lizzie_gurl
Starting Member

11 Posts

Posted - 2014-06-03 : 20:58:52

Hi Guys,

Need your help on converting date and/or time from character string.
Got this error message: "Msg 241, Level 16, State 1, Line 7
Conversion failed when converting date and/or time from character string."

Here's my query:
DECLARE @StartDate AS varchar(30)
DECLARE @EndDate AS varchar(30)

SET @StartDate = (CONVERT(varchar(20),'01-05-2014', 101))
SET @EndDate = (CONVERT(varchar(20),'31-05-2014', 101))

Select
CASE
when START_EXPENSE_DATE>=@StartDate
AND START_EXPENSE_DATE<=@EndDate
then 'May'
else START_EXPENSE_DATE
END
AS EXPENSE_DATE
FROM ORA.AP_EXPENSE_REPORT_LINES_ALL
where [START_EXPENSE_DATE] >='01/MAY/2014'
AND ITEM_DESCRIPTION like '%AIR%'
AND [AMOUNT] >'50'
AND (
[ITEMIZATION_PARENT_ID] IS NULL
OR [ITEMIZATION_PARENT_ID] != -1
)

Please help!

Thanks!

Lizzie :-)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-06-03 : 23:13:17

[code]
DECLARE @StartDate AS ~~varchar(30)~~ DATETIME
DECLARE @EndDate AS ~~varchar(30)~~ DATETIME

SET @StartDate = ~~(CONVERT(varchar(20),'01-05-2014', 101))~~ '2014-05-01'
SET @EndDate = ~~(CONVERT(varchar(20),'31-05-2014', 101))~~ '2014-05-31'
[/code]

KH
[spoiler]Time is always against us[/spoiler]

spudsully
Starting Member

2 Posts

Posted - 2014-06-03 : 23:41:55

Be careful with the datetime value as your query will limit data <= '2014-05-31 00:00:00.000' instead of '2014-05-31 11:59:59.000' - pending your Start_Expense_Date is a Date only field.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources