Community FAQ
cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
pnobels
Engaged Sweeper
Hi,

i'm using customer12 as a date field. Goal is to use it as the date a support contract expires. However in reportbuilder it still shows up as nvarchar.
Eventually i wanted to create a filter which gives me all assets which have a date < 7 days.

Fairly easy i thought. Doing something like :

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssetCustom.Custom12
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where DateDiff(day, tblAssetCustom.Custom12, GetDate()) >= 7

I provided only one asset with a date. Rest of assets have an empty field for Custom12. When i run the report i get a lot of entries (more then the one i filled in) where nothing is filled in the Custom12 field.

So i tried some other stuff to debug :

Where IsDate(tblAssetCustom.Custom12) Is Not Null -> returns me a lot of assets (with empty Custom12 field).

So something's wrong. I expect to receive only one entry here...

1 ACCEPTED SOLUTION
pnobels
Engaged Sweeper
Okay, figured it out... :

Where tblAssetCustom.Custom12 != '' And DateDiff(day, GetDate(),
Convert(datetime,tblAssetCustom.Custom12)) <= 7

View solution in original post

1 REPLY 1
pnobels
Engaged Sweeper
Okay, figured it out... :

Where tblAssetCustom.Custom12 != '' And DateDiff(day, GetDate(),
Convert(datetime,tblAssetCustom.Custom12)) <= 7

Reports & Analytics

Ask about reports you're interested in and share reports you've created. Subscribe to receive daily updates of reports shared in the Community.

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now