sql server - SQL Case statement returns wrong value -
i have following sql statement designed give field t1.dwposteddatekey value if t2.inventorystatuscode changes 'p'. starting value of t1.dwposteddatekey null, , if t2.inventorystatuscode doesn't change, want stay null.
select t1.datekey, t1.dwposteddatekey, (case when t2.inventorystatuscode = 'p' , (t1.inventorystatuskey != t2.inventorystatuskey) convert(int, getdate(), 112) else t1.dwposteddatekey end) table1 t1 inner join table2 t2 on t1.key = t2.key
the problem don't null ? here example of results:
datekey dwposteddatekey (no column name) 20150413 null 42106 20150413 null 42106 20150413 null 42106 20150413 20150414 20150414 20150413 20150414 20150414 20150413 20150414 20150414
what 42106 doing in there? want remain null value.
the problem line convert(int, getdate(), 112)
, in int
.
it converting datetime
int
, sql server when returning number of days since 1/1/1900. (that number see)
this return need
convert(varchar, getdate(), 112)
if need integer try this
cast(convert(varchar, getdate(), 112) integer)
hope helps
Comments
Post a Comment