SQL Server - convert not working on varchar data type

I am trying to do a comparison on when a report last ran using getdate()-180 which is working, but when I try to convert the field in the database nothing changes.

SELECT

 

ObjName as "Report Name"
,SI_RECURRING as "Recurring Status"
,LastModifyTime as "Last Run"
,ScheduleStatus as "Schedule Status"
,convert(varchar, getdate()-360/2, 101) as "Cut Off Date"
FROM CMS_InfoObjects3
WHERE convert(VARCHAR, LastModifyTime,114) > convert(varchar, getdate()-360/2, 114)
AND ObjName like '%.rpt'
GROUP BY ObjName, LastModifyTime, ScheduleStatus, SI_RECURRING
order byObjName, LastModifyTime desc;

convert(VARCHAR, LastModifyTime,114) does not seem to be converting to the same format as the getdate convert.

 

The 114 option of CONVERT Function will convert the souce expression or DateTime to below mentioned format:

14

114

-

hh:mi:ss:mmm(24h)

SELECT

 

CONVERT (NVARCHAR,GETDATE(),114) -- This will convert the datetime to time 24 hr format.
SELECT CONVERT (NVARCHAR,'06:49:02.810',114)  -- This will give the same result what value we have given in expression option.

If yourLastModifyTime column has a time value then the output will be the same result as the column has.

Thanks

 

Hi Venkat the field name indicates that it is a date time, but it is not it is data type varchar(30) the data looks like.

2008 04 01 04 52 38 398 which I am assuming is yyyy mm dd hh mm dd ttt

and no matter how I try to format (convert) the field I get no response it stays the same.

I am using SQL server 8.0 and the collation is SQL_Latin1_General_CP1_CI_AS

Ideally it would be nice if I could get it to format to 101 or dd/mm/yyyy

thanks for your assistance.

u don't need to date compairing after convert it into varchar , event varchar comparing chatacter not date
u just change your where condtion as follows this will work perfectly
WHERE LastModifyTime > (getdate()-360/2)

Sorry Web Star

 

 

 

Gives me Syntax error converting datetime from character string.

 

Something went wrong with my reply....

SELECT

 

ObjName as "Report Name"
,SI_RECURRING as "Recurring Status"
,LastModifyTime as "Last Modified Date"
,ScheduleStatus as "Schedule Status"
,getdate()-180 as "Cut Off Date"
FROM CMS_InfoObjects3
WHERE LastModifyTime > (getdate()-180)
AND ObjName like '%.rpt'
GROUP BY ObjName, LastModifyTime, ScheduleStatus, SI_RECURRING
order by ObjName, LastModifyTime desc;

This results in an error:  Syntax error converting datetime from character string.

 

Means in your table date are stored in varchar datatype column, it column is datatime than above query works if varchar than u need to convert it into datetime not in varchar as
WHERE Convert(Datetime, LastModifyTime) > (getdate()-180)
hope this helps

Still getting this error

Syntax error converting datetime from character string.

I am not sure if there is a problem with the actual data having gaps between the characters.

yyyy mm dd hh mm ss ttt

normally the getdate is formatted yyyy-mm-dd hh:mm:ss,ttt

Would this be causing an issue with the convert process as nothing I am trying is working.

:-(