Author |
Topic |
kjamal
Starting Member
1 Post |
Posted - 2013-07-16 : 09:32:08
|
I have a very old database that has configured Date as a numeric value instead of a DateTime value making it really hard to filter. So in my query I'm trying to cast it into a DateTime value but I'm receiving some errors. Is it possible for someone to offer some modifications to my existing query for it to run error free?My Query: SELECT CAST (DATEADD(HOUR,-4,CONVERT(DATETIME,LEFT([Date],8)+' '+SUBSTRING([Date],10,2)+':'+SUBSTRING([Date],12,2)+':'+SUBSTRING([Date],14,2)+'.'+SUBSTRING([Date],15,3))) AS [Date]) 'Date',LEFT(CAST(DATEADD(HOUR,-4,CONVERT(DATETIME,LEFT([Date],8)+' '+SUBSTRING([Date],10,2)+':'+SUBSTRING([Date],12,2)+':'+SUBSTRING([Date],14,2)+'.'+SUBSTRING([Date],15,3))) AS [Time]),8) 'Time',[Object] AS [Dataset],SUBSTRING(Parms,1,6) AS [Media]FROM (Select CONVERT(VARCHAR(18),[Date]) [Date], [Object],MsgID,ParmsFROM JnlDataSectionWHERE CAST(substring(convert(varchar(50), [Date]), 0, 5) + '-' +substring(convert(varchar(50), [Date]), 5, 2) + '-' +substring(convert(varchar(50), [Date]), 7, 2) AS DATETIME)) =(Date, DATEADD(day,-1, getdate())) AWhere MsgID = '325' ANDSUBSTRING(Parms,1,6) = 'V40449' Order By Date DESC;Msg 4145, Level 15, State 1, Line 21An expression of non-boolean type specified in a context where a condition is expected, near ')'. |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-07-16 : 10:04:34
|
quote: Originally posted by kjamal I have a very old database that has configured Date as a numeric value instead of a DateTime value making it really hard to filter. So in my query I'm trying to cast it into a DateTime value but I'm receiving some errors. Is it possible for someone to offer some modifications to my existing query for it to run error free?My Query: SELECT CAST (DATEADD(HOUR,-4,CONVERT(DATETIME,LEFT([Date],8)+' '+SUBSTRING([Date],10,2)+':'+SUBSTRING([Date],12,2)+':'+SUBSTRING([Date],14,2)+'.'+SUBSTRING([Date],15,3))) AS [Date]) 'Date',LEFT(CAST(DATEADD(HOUR,-4,CONVERT(DATETIME,LEFT([Date],8)+' '+SUBSTRING([Date],10,2)+':'+SUBSTRING([Date],12,2)+':'+SUBSTRING([Date],14,2)+'.'+SUBSTRING([Date],15,3))) AS [Time]),8) 'Time',[Object] AS [Dataset],SUBSTRING(Parms,1,6) AS [Media]FROM (Select CONVERT(VARCHAR(18),[Date]) [Date], [Object],MsgID,ParmsFROM JnlDataSectionWHERE CAST(substring(convert(varchar(50), [Date]), 0, 5) + '-' +substring(convert(varchar(50), [Date]), 5, 2) + '-' +substring(convert(varchar(50), [Date]), 7, 2) AS DATETIME)) =(Date, DATEADD(day,-1, getdate())) AWhere MsgID = '325' ANDSUBSTRING(Parms,1,6) = 'V40449' Order By Date DESC;Msg 4145, Level 15, State 1, Line 21An expression of non-boolean type specified in a context where a condition is expected, near ')'.
You have brackets mis-aligned. The following will eliminate the syntax errors, but that doesn't mean the logic is correct, or that this is what you should be using. If you can post a sample of the numeric representation of the data and what you want it to be converted to, someone will be able to offer a simpler way of doing that conversion:SELECT CAST (DATEADD(HOUR,-4,CONVERT(DATETIME,LEFT([Date],8)+' '+SUBSTRING([Date],10,2)+':'+SUBSTRING([Date],12,2)+':'+SUBSTRING([Date],14,2)+'.'+SUBSTRING([Date],15,3))) AS [Date]) 'Date',LEFT(CAST(DATEADD(HOUR,-4,CONVERT(DATETIME,LEFT([Date],8)+' '+SUBSTRING([Date],10,2)+':'+SUBSTRING([Date],12,2)+':'+SUBSTRING([Date],14,2)+'.'+SUBSTRING([Date],15,3))) AS [Time]),8) 'Time',[Object] AS [Dataset],SUBSTRING(Parms,1,6) AS [Media]FROM ( Select CONVERT(VARCHAR(18),[Date]) [Date], [Object], MsgID, Parms FROM JnlDataSection WHERE CAST(substring(convert(varchar(50), [Date]), 0, 5) + '-' + substring(convert(varchar(50), [Date]), 5, 2) + '-' + substring(convert(varchar(50), [Date]), 7, 2) AS DATETIME) = CONVERT(Date, DATEADD(day,-1, getdate()))) AWhere MsgID = '325' ANDSUBSTRING(Parms,1,6) = 'V40449' Order By Date DESC; |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-16 : 12:42:33
|
Please give an example of Date column. N 56°04'39.26"E 12°55'05.63" |
|
|
|
|
|