how to convert if the col data type is nvarchar

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

how to convert if the col data type is nvarchar

Author

Topic

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-11-18 : 11:49:19

column is CHDEFAULTLOGIN with datatype nvarchar

SELECT TOP (100) PERCENT ITOKENNUM, BENABLED, CHSERIALNUM, DATEPIN, DATEENABLED, DATETOKENASSIGNMENT, DATELASTLOGIN, IUSERNUM,
CHLASTNAME, CHFIRSTNAME, CHDEFAULTLOGIN, CHKEY, CHDATA, iReplacingTokenNum, iType
FROM dbo.ACE_USERS
WHERE (CHDATA <> '') AND (CHSERIALNUM = '') AND (CHDEFAULTLOGIN LIKE 'zz%')
and (CHDEFAULTLOGIN like 'zzz%') and (CHDEFAULTLOGIN like 'zzz%') and (DateDiff(d,Cast((substring(CHDEFAULTLOGIN , 4, 2) + '/' + substring(CHDEFAULTLOGIN , 6, 2) + '/' + substring(CHDEFAULTLOGIN , 8, 2)) as datetime), Getdate()) >= 30)
ORDER BY CHDEFAULTLOGIN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 11:51:39

how will values of CHDEFAULTLOGIN be?

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-11-18 : 12:20:13

Thank you for the response. here this is how the data is.

ZZ
zz01042008CHENZ3
zz01102008honors
zz01312008COLAKK
zz02072008INTSHEWA
zz030308GALLIS
zz041007DISCOG
zz043008mattet1
zz050707EDENG
zz070708MAKEEI
zz081707TCSBALAM
zz090706STEELG
zz122006WALKEL1
zzz002222008WUY6
zzz01/02/2008TURNEA
zzz01/08/2008wachsv
zzz01/21/2008PARADN
zzz01/24/2008modreesj
zzz01/29/2008SPRAFR
zzz010108barbeg
zzz010108BORAS
zzz010108BOSUKA
zzz010108CammaL
zzz010108chengj
zzz010108CONDUM
zzz010108FISCHT
zzz010108HARIHC
zzzzALMEIA2-28-bulk
zzzzanjan1-18-07-bulk
zzzzatiass1-18-07-bulk
zzzzAXTELH2-28-bulk
zzzzBALLM112-18-06-Bulk
zzzzbarren2-28-bulk
zzzzBAUGHM12-28-bulk
zzzzBEBIEG2-28-bulk
zzzzBELLT2-28-bulk
zzzzBENGALLJ12-18-06-Bulk
zzzzBENMOYLD12-18-06-Bulk
zzzzwrongly craeted

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 12:29:05

you need to first extract only date values and then apply datediff on them. else it will break.

Maachie
Yak Posting Veteran

69 Posts

Posted - 2008-11-18 : 12:48:39

How would I do that? Any suggestion with the data being like this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-18 : 13:31:22